You can use the OBJECTPROPERTY() function in SQL Server to see whether or not a table has a DEFAULT constraint.

To do this, pass the table’s object ID as the first argument, and TableHasDefaultCnst as the second argument. The function returns a 1 or a 0 depending on whether or not it has a DEFAULT constraint.

A return value of 1 means that the table has a DEFAULT constraint, and a value of 0 means that it’s not.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE WideWorldImportersDW;
SELECT OBJECTPROPERTY(1141579105, 'TableHasDefaultCnst') AS [TableHasDefaultCnst];

Result:

+-----------------------+
| TableHasDefaultCnst   |
|-----------------------|
| 1                     |
+-----------------------+

In this case, the WideWorldImportersDW database has a table with the ID provided, and it has a DEFAULT constraint.

Example 2 – Getting the Object ID

If you know the table’s name, but not its ID, you can use the OBJECT_ID() function to retrieve the ID based on its name.

Example:

SELECT OBJECTPROPERTY(OBJECT_ID('Dimension.City'), 'TableHasDefaultCnst') AS [TableHasDefaultCnst];

Result:

+-----------------------+
| TableHasDefaultCnst   |
|-----------------------|
| 1                     |
+-----------------------+

This is the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('Dimension.City') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Dimension.City'), 'TableHasDefaultCnst') AS [TableHasDefaultCnst];

Result:

+-------------+-----------------------+
| Object ID   | TableHasDefaultCnst   |
|-------------+-----------------------|
| 1013578649  | 1                     |
+-------------+-----------------------+

Example 3 – When the Table does NOT have a DEFAULT Constraint

Here’s what happens when the table doesn’t have a DEFAULT constraint.

SELECT OBJECTPROPERTY(OBJECT_ID('Fact.Movement'), 'TableHasDefaultCnst') AS [TableHasDefaultCnst];

Result:

+-----------------------+
| TableHasDefaultCnst   |
|-----------------------|
| 0                     |
+-----------------------+

In this case, the object is a table, it’s just that it doesn’t have a DEFAULT constraint.

Example 4 – When the Object Isn’t a Table

Here’s what happens when the database does contain an object with the ID, but that object isn’t a table.

SELECT OBJECTPROPERTY(
    OBJECT_ID('Sequences.ReseedAllSequences'), 
    'TableHasDefaultCnst') AS [TableHasDefaultCnst];

Result:

+-----------------------+
| TableHasDefaultCnst   |
|-----------------------|
| NULL                  |
+-----------------------+

Example 5 – Object Doesn’t Exist

SQL Server assumes that the object ID is in the current database context. If you pass in an object ID from a different database, you’ll either get a NULL result or you’ll get incorrect results.

SELECT 
  OBJECTPROPERTY(OBJECT_ID('InvalidObject'), 'TableHasDefaultCnst') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'TableHasDefaultCnst') AS [12345678];

Result:

+-----------------+------------+
| InvalidObject   | 12345678   |
|-----------------+------------|
| NULL            | NULL       |
+-----------------+------------+

In this case the database contains no objects of that name or ID, and so I get a NULL result.

You’ll also get NULL on error or if you don’t have permission to view the object.