In SQL Server you can use the OBJECTPROPERTY() function to check an object’s type. More specifically, you can check whether it is or isn’t a specific type.
For example, the IsTable property tells you whether or not it’s a table, the IsView property tells you whether or not it’s a view, etc.
This article offers a few basic examples that check whether an object is a table, view, stored procedure, or table-valued function.
If you ever need to find out whether a database has any tables that don’t have a primary key, you can run any of the following scripts in SQL Server to return just those tables.
All of these scripts take advantage of the OBJECTPROPERTY() function. This function accepts a TableHasPrimaryKey argument that you can check for a value of 0. If it’s 0, the table doesn’t have a primary key. If it’s 1 it does. Therefore, you can also use this function to return all tables with a primary key.
These scripts simply return the name of the table and its schema, but you can always modify them to return more columns.
If you ever find yourself needing to find all tables that have a primary key in SQL Server, this article can help.
This article offers seven ways to return all tables in the current database that have a primary key.
Note that most of these examples return only the tables – not the primary keys themselves. If you want a list of primary keys, see 11 Ways to Return a Primary Key in SQL Server.
This article offers seven ways to return all tables that have foreign keys in a database in SQL Server.
Each table is returned just once, regardless of how many foreign keys it may have. This is different to returning all foreign keys, along with their tables. If you want to do that, see 11 Ways to Return Foreign Keys in SQL Server.
All examples here query the same database, and therefore return the same result.
In SQL Server, you can use the APP_NAME() function to get the application name for the current session. This assumes the application sets that name value.
You can use this function to distinguish between different applications, as a way to perform different actions for those applications.
Note that the client provides the application name, and so the result returned by this function simply reflects whatever name the client provides. For this reason, Microsoft advises that this function should not be used for security checks.
In SQL Server, you can use the COL_LENGTH() function to get the length of a column. More specifically, the function returns the defined length of the column, in bytes.
The function accepts two arguments: the table name, and the column name.
In SQL Server, you can use the COL_NAME() function to return a column’s name, based on its ID and its parent table ID.
This can be useful when querying a table that only stores the column ID and the parent table’s ID.
The COL_NAME() function requires two arguments: the table ID, and the column ID.
In SQL Server, the COLUMNPROPERTY() function returns column or parameter information.
For example, you can use it to return information about a column in a table, a parameter for a stored procedure, etc
It accepts three arguments: the ID of the table or procedure, the applicable column or parameter, and the property you want information about.
In SQL Server, the DATABASEPROPERTYEX() function returns the current setting of the specified database option or property for the specified database.
For example, you can use it to return the collation of a database, check the level of user access, etc.
It accepts two arguments: the database, and the property you want information about.
In SQL Server, you might’ve encountered the OBJECTPROPERTY() function, only to find out that there’s also a OBJECTPROPERTYEX() function that appears to do exactly the same thing.
What’s going on here? Why the need for two functions that do the same thing?
My understanding is that Microsoft chose to add OBJECTPROPERTYEX() to extend the functionality of OBJECTPROPERTY(), rather than to introduce changes to OBJECTPROPERTY() that would potentially break existing code on legacy systems.
So there are some differences between the two functions.