Problem solve Get help with specific problems with your technologies, process and projects.

Inspecting property settings with T-SQL metadata functions

There are plenty of other uses for SQL Server 2008 metadata functions beyond just the basics. This includes the ability to view object properties, such as columns and data types.

Part 1 | Part 2 | Part 3

Part one of this series examined the basics of metadata functions and how to use them to view object names and IDs. This article uncovers other metadata function uses, such as how to view property settings for various objects. Part three then looks into using database file and filegroup information.

Viewing object properties

This next set of metadata functions allows you to retrieve the property settings for various database objects. You must supply at least two arguments for most of these functions. One of those arguments identifies the specific object and the other identifies the property settings you want to return.

NOTE: To view a list of the properties that a function supports, see the topic for that function in SQL Server Books Online. The topic provides a description of each property and details about the values returned for that property.

The first function we’ll look at is DATABASEPROPERTYEX. The following SELECT statement contains three instances of the function with a different property specified for each:

SELECT
   DATABASEPROPERTYEX('AdventureWorks2008',
    'Collation') AS Collation,
   DATABASEPROPERTYEX('AdventureWorks2008',
    'IsAutoShrink') AS AutoShrink,
   DATABASEPROPERTYEX('AdventureWorks2008',
    'IsFullTextEnabled') AS FullTextEnabled;

As you can see, I first specified the name of the database (AdventureWorks2008) and then specified the property name (Collation, IsAutoShrink and IsFullTextEnabled, respectively). The SELECT statement returns the following results:

Collation AutoShrink FullTextEnabled
SQL_Latin1_General_CP1_CI_AS 0 1

The Collation property returns the default collation for the named database, while the IsAutoShrink property indicates whether the database files are set up for autoshrink mode. For properties that start with “Is”, a returned value of 1 indicates True and a returned value of 0 indicates False. As the results show, the AdventureWorks2008 database is not set up for autoshrink mode; however, the IsFullTextEnabled property indicates that the database is full-text enabled.

The OBJECTPROPERTYEX function returns the property setting for a scope-enabled object. The function takes two arguments -- the object ID and the property name. For instance, the following SELECT statement returns property settings for the SalesPerson table, which has an object ID of 1298103665:

SELECT
   OBJECTPROPERTYEX(1298103665, 'BaseType')
    AS BaseType,
   OBJECTPROPERTYEX(1298103665, 'IsIndexed')
    AS Indexed,
   OBJECTPROPERTYEX(1298103665, 'IsUserTable')
    AS UserTable;

I’ve specified three properties: BaseType identifies the base type of the object, IsIndexed indicates whether an index is defined on the object and IsUserTable indicates whether the object is a user-defined table. The SELECT statement returns the following results:

BaseType Indexed UserTable
U 1 1


Because the BaseType property returns a value of U, we know that the object is a user-defined table. The value U is the object’s principle ID. You can find a list of principle IDs in the topic “sys.objects (Transact-SQL)” in SQL Server Books Online.

The IsIndexed property returns a True (1), so we know the table is indexed and the IsUserTable property also returns a True, so we know the object is a user-defined table.

If you want to view properties related to indexes, you can use the INDEXPROPERTY function. The function takes three arguments -- the object ID on which the index is defined, the name of the index and the name of the property. For example, the following SELECT statement retrieves information on the PK_SalesPerson_BusinessEntityID index, which is defined on the SalesPerson table:

SELECT
   INDEXPROPERTY(1298103665,
    'PK_SalesPerson_BusinessEntityID',
    'IsClustered') AS ClusteredInd,
   INDEXPROPERTY(1298103665,
    'PK_SalesPerson_BusinessEntityID',
    'IsFullTextKey') AS FullTextKey,
   INDEXPROPERTY(1298103665,
    'PK_SalesPerson_BusinessEntityID',
    'IsUnique') AS UniqueInd;

For each instance of the INDEXPROPERTY function, I provide the object ID for the SalesPerson table, then the name of the index. Finally, I specify the property. The IsClustered property indicates whether the index is clustered, the IsFullTextKey property shows whether the index is the full-text key for the table and the IsUnique property tells you whether this is a unique index.

As the following results show, the PK_SalesPerson_BusinessEntityID index is a clustered, unique index, but it is not the full-text key:

ClusteredInd  FullTextKey UniqueInd
1 0 1

Another useful function is COLUMNPROPERTY, which returns the property settings on a specific column. As with the INDEXPROPERTY function, the COLUMNPROPERTY function takes three arguments -- the ID of the object on which the column is defined, the name of the column and the name of the property, as shown in the following example:

SELECT
   COLUMNPROPERTY(1298103665, 'SalesQuota',
    'AllowsNull') AS AllowsNull,
   COLUMNPROPERTY(1298103665, 'SalesQuota',
    'ColumnId') AS ColumnId,
   COLUMNPROPERTY(1298103665, 'SalesQuota',
    'IsComputed') AS Computed;

In this example, I’m retrieving property information on the SalesQuota column of the SalesPerson table. The AllowsNull property indicates whether the column allows null values, the ColumnID property returns the column’s assigned ID and the IsComputed property shows whether this is a computed column. As the following results indicate, the SalesQuota column permits null values, the column’s ID is 3 and it is not a computed column:

AllowsNull ColumnId Computed
1 3 0

One other property-related function we’ll review is TYPEPROPERTY, which returns information about a specific data type. The function takes two arguments -- the name of the data type and the name of the property. The following example returns information about the money data type:

SELECT
   TYPEPROPERTY('money', 'AllowsNull')
    AS AllowsNull,
   TYPEPROPERTY('money', 'Precision')
    AS  Precision,
   TYPEPROPERTY('money', 'Scale')
    AS Scale;

After I specify the first argument -- the name of the data type -- I then specify the property. The AllowsNull property shows whether the data type permits null values, the Precision property indicates the maximum number of digits or characters that the value can have and the Scale property tells you the number of permitted decimal places. In the SELECT statement above, the three properties return the following results:

AllowsNull Precision Scale 
1 19 4

As you can see, the money data type permits null values and supports up to 19 characters with four decimal places.

Part three: Uncovering file information with metadata functions

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.

This was last published in September 2010

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close