Tag: tables

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 →

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 →

3 Ways to Count the Number of System Tables in a SQL Server Database

Posted on by Ian

Here’s an article that presents three ways to quickly determine how many system tables are in the current database in SQL Server.

All three options use the COUNT() function while querying the sys.objects system catalog view. They all result in the same output, so you really don’t need to go past the first option. But I’ll list them anyway.

Continue reading →

11 Ways to Return Foreign Keys in a SQL Server Database using T-SQL

Posted on by Ian

This article presents eleven different approaches to retrieving foreign key information in SQL Server. Some of these will be more useful than others, depending on the situation.

Some of these methods can be modified to return further information. For example, you could perform joins on various system views to return more data.

Continue reading →

What is a Table?

Posted on by Ian

A database table is a structure that organises data into rows and columns – forming a grid.

Tables are similar to a worksheets in spreadsheet applications. The rows run horizontally and represent each record. The columns run vertically and represent a specific field. The rows and columns intersect, forming a grid. The intersection of the rows and columns defines each cell in the table.

Continue reading →