Inspecting property settings with T-SQL metadata functions

Inspecting property settings with T-SQL metadata functions

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in September 2010

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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top