By: Arshad Ali | Comments | Related: > Functions System
Problem
SQL Server provides several system meta data functions which allow users to get property values of different SQL Server objects/securables. Although you can use the Catalog views or Dynamic Management Views to get the information, sometimes the system meta data functions simplify the process. In this tip, I am going to demonstrate some of the system meta data functions and their usage in different use case scenarios.
Solution
SQL Server provides several system meta data functions to get property values for different object types. Each system meta data function has a set of properties you can query using the system meta data function. In my last tip "SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc." I provided examples about the SERVERPROPERTY, DATABASEPROPERTY, FILEGROUPPROPERTY, FILEPROPERTY, CONNECTIONPROPERTY and SESSIONPROPERTY functions. In this tip, I am going to outline examples on the following functions:
- OBJECTPROPERTY
- INDEXPROPERTY
- COLUMNPROPERTY
OBJECTPROPERTY and OBJECTPROPERTYEX
The OBJECTPROPERTY and OBJECTPROPERTYEX system meta data functions return similar results and they both can be used to get the information about schema scoped objects, i.e. tables, views, stored procedures, user defined functions, etc. For these functions, you need to pass the object id and respective property to get its value. As in the case of the DATABASEPROPERTY and DATABASEPROPERTYEX functions, it is recommended to use DATABASEPROPERTYEX as DATABASEPROPERTY has been marked as deprecated in a future SQL Server release. Likewise I assume the OBJECTPROPERTYEX function is recommended over the OBJECTPROPERTY function, but I did not find any documentation supporting that assumption, so I would suggest you to search the SQL Server documentation before implementing it in your code.
In terms of using the functions, properties are available to check some of these items:
- IsTable
- IsView
- IsProcedure
- TableHasIndex
- TableHasClustIndex
For a complete list of the properties, click here. The script below provides examples of the OBJECTPROPERTY system meta data function.
USE AdventureWorks
GO
DECLARE @ObjectID INT = OBJECT_ID('HumanResources.Employee')
SELECT
'IsTable' AS [ObjectProperty], OBJECTPROPERTY(@ObjectID, 'IsTable') AS [Value]
UNION ALL
SELECT 'IsVIew', OBJECTPROPERTY(@ObjectID, 'IsVIew')
UNION ALL
SELECT 'IsProcedure', OBJECTPROPERTY(@ObjectID, 'IsProcedure')
UNION ALL
SELECT 'IsScalarFunction', OBJECTPROPERTY(@ObjectID, 'IsScalarFunction')
UNION ALL
SELECT 'IsTableFunction', OBJECTPROPERTY(@ObjectID, 'IsTableFunction')
UNION ALL
SELECT 'IsExecuted', OBJECTPROPERTY(@ObjectID, 'IsExecuted')
UNION ALL
SELECT 'TableHasIndex', OBJECTPROPERTY(@ObjectID, 'TableHasIndex')
UNION ALL
SELECT 'TableHasClustIndex', OBJECTPROPERTY(@ObjectID, 'TableHasClustIndex')
UNION ALL
SELECT 'TableHasPrimaryKey', OBJECTPROPERTY(@ObjectID, 'TableHasPrimaryKey')
UNION ALL
SELECT 'TableHasIdentity', OBJECTPROPERTY(@ObjectID, 'TableHasIdentity')
UNION ALL
SELECT 'HasAfterTrigger', OBJECTPROPERTY(@ObjectID, 'HasAfterTrigger')
UNION ALL
SELECT 'HasInsteadOfTrigger', OBJECTPROPERTY(@ObjectID, 'HasInsteadOfTrigger')
GO
INDEXPROPERTY
With the INDEXPROPERTY system meta data function you can get different index and statistics properties for a table. It accepts three parameters, object id, index/statistics name and the property whose value you need to know. For example, to find out if the index is unique use the IsUnique property, to find out if the index is disabled use the IsDisabled property. Likewise to find out the index fill factor and index depth used, the IndexFillFactor and IndexDepth properties would be referenced. For complete list of properties, click here. The script below provides examples on how you can use the INDEXPROPERTY system meta data function with different properties.
USE AdventureWorks;
GO
DECLARE
@ObjectID INT = OBJECT_ID('HumanResources.Employee')
SELECT
'IsClustered' AS [IndexProperty], INDEXPROPERTY(@ObjectID, 'PK_Employee_EmployeeID', 'IsClustered') AS [Value]
UNION ALL
SELECT 'IsUnique', INDEXPROPERTY(@ObjectID, 'PK_Employee_EmployeeID', 'IsUnique')
UNION ALL
SELECT 'IsDisabled', INDEXPROPERTY(@ObjectID, 'PK_Employee_EmployeeID', 'IsDisabled')
UNION ALL
SELECT 'IndexFillFactor', INDEXPROPERTY(@ObjectID, 'PK_Employee_EmployeeID', 'IndexFillFactor')
UNION ALL
SELECT 'IndexDepth', INDEXPROPERTY(@ObjectID, 'PK_Employee_EmployeeID', 'IndexDepth')
GO
COLUMNPROPERTY
The COLUMNPROPERTY system meta data function is used to get the information about a column of the table or a parameter of the stored procedure. For example, to check if the column is an identity column you use IsIdentity property. To check if the column allows null you use the AllowsNull property. To check if the column is computed or it can be indexed you use IsComputed or IsIndexable properties respectively. For complete list of properties, click here. The script below demonstrates how you can use the COLUMNPROPERTY system meta data function with different properties.
USE AdventureWorks;
GO
DECLARE
@ObjectID INT = OBJECT_ID('HumanResources.Employee')
SELECT
'IsIdentity' AS [ColumnProperty], COLUMNPROPERTY(@ObjectID, 'EmployeeID', 'IsIdentity') AS [Value]
UNION ALL
SELECT 'AllowsNull', COLUMNPROPERTY(@ObjectID, 'EmployeeID', 'AllowsNull')
UNION ALL
SELECT 'IsComputed', COLUMNPROPERTY(@ObjectID, 'EmployeeID', 'IsComputed')
UNION ALL
SELECT 'IsIndexable', COLUMNPROPERTY(@ObjectID, 'EmployeeID', 'IsIndexable')
UNION ALL
--Sparse column feature is introduced in SQL Server 2008
SELECT 'IsSparse', COLUMNPROPERTY(@ObjectID, 'EmployeeID', 'IsSparse')
GO
Apart from the above discussed system meta data functions, there are a couple of more to be aware of:
- TYPEPROPERTY which returns information about a data type
- ASSEMBLYPROPERTY which returns information about an assembly stored in SQL Server etc.
- For complete list of these system meta data functions, click here.
Please note, SQL Server 2008 allows user to view the metadata of the object/securable on which the user has the permission or he/she him/herself owns it. Hence above mentioned system meta data functions will return NULL if you specify the wrong property name or the user does not have permission on the object/securable.
Next Steps
- The next time you are searching for SQL Server system information, consider the scripts in this tip as a point of reference.
- Check out the previous tip in this series - SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.
- Review Understanding Catalog Views in SQL Server 2005 and 2008 tip.
- Review Understanding Dynamic Management Views and Functions in SQL Server 2008 tip.
- Review my previous tips.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
