Tag: t-sql

Check Whether a Table Has a TIMESTAMP Column in SQL Server with OBJECTPROPERTY()

Posted on by Ian

You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has a timestamp column.

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

A return value of 1 means that the table does have a timestamp column, and a value of 0 means that doesn’t.

This also works for columns that have been defined as rowversion (timestamp is the deprecated synonym for rowversion).

Continue reading →

Check Whether a Table Has a Foreign Key in SQL Server with OBJECTPROPERTY()

Posted on by Ian

You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has one or more foreign key constraints.

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

A return value of 1 means that the table does have a foreign key constraint, and a value of 0 means that doesn’t. A return value of 1 applies regardless of how many foreign keys the table has (as long as it has at least one).

If you want an actual list of the foreign keys, see List All Foreign Keys on a Table in SQL Server.

Continue reading →

Check if a Table is Referenced by a Foreign Key in SQL Server with OBJECTPROPERTY()

Posted on by Ian

You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table is referenced by a foreign key.

To do this, pass the table’s object ID as the first argument, and TableHasForeignRef as the second argument. The function returns a 1 or a 0 depending on whether or not it is referenced by a foreign key.

A return value of 1 means that the table is referenced by a foreign key, and a value of 0 means that it’s not.

Note that the examples presented here don’t list out the foreign keys or their tables or anything like that. They simply return a true/false value that you can use to test whether or not a table is referenced by a foreign key. If you need to list out all foreign keys that reference a given table, see Return All Foreign Keys that Reference a Given Table in SQL Server. The examples in that article list out each foreign key, as well as the foreign key table/s, and the primary key table.

Continue reading →

See if a Table has a DEFAULT Constraint in SQL Server using OBJECTPROPERTY()

Posted on by Ian

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.

Continue reading →

Find Out if an Object is a User-Defined Table in SQL Server with OBJECTPROPERTY()

Posted on by Ian

You can use the OBJECTPROPERTY() function in SQL Server to check whether an object is a user-defined table or not.

To do this, pass the object ID as the first argument, and IsUserTable as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a user-defined table.

A return value of 1 means that it is a user-defined table, and a value of 0 means that it’s not.

Continue reading →

Find Out if an Object is a Table-Valued Function in SQL Server with OBJECTPROPERTY()

Posted on by Ian

You can use the OBJECTPROPERTY() function in SQL Server to check whether an object is a table-valued function or not.

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

A return value of 1 means that it is a table-valued function, and a value of 0 means that it’s not.

Continue reading →

Use OBJECTPROPERTY() to Find Out if a Table is a System Table in SQL Server

Posted on by Ian

The OBJECTPROPERTY() function in SQL Server enables you to check an object for a specific property.

You can use this function to check whether an object is a system table or not. To do this, pass the object ID as the first argument, and IsSystemTable as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a system table (1 means that it is a system table, and 0 means that it’s not).

Continue reading →

Check if an Object is a Primary Key with OBJECTPROPERTY() in SQL Server

Posted on by Ian

You can use the OBJECTPROPERTY() function in SQL Server to check whether or not an object is a primary key.

To find out whether an object is a primary key, pass the object ID as the first argument, and IsPrimaryKey as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a primary key.

A return value of 1 means that it is a primary key, and a value of 0 means that it’s not.

Continue reading →