Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 Functions for Tables, Views, Indexes, Columns, Stored Procedures


By:   |   Last Updated: 2010-05-12   |   Comments   |   Related Tips: More > 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.

-- Script #1 - 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


SQL Server provides several system meta data functions to get property values for different object types

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.

--Script #2 - INDEXPROPERTY System Function

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


With the INDEXPROPERTY system meta data function you can get different index and statistics properties for a table

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.

--Script #3 - COLUMNPROPERTY System Meta Data Function

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


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

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


Last Updated: 2010-05-12


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools