Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Use Property Functions to Query Metadata

There are three property functions for querying the attributes of database objects: OBJECTPROPERTY, COLUMNPROPERTY, and DATABASEPROPERTY. This solution looks at the OBJECTPROPERTY function and describes the kind of data you can retrieve with it.


advertisement
nce you get past the point of creating a solid database design and ensuring the integrity of your database, inevitably you need to know about the objects in your database. SQL Server has a variety of objects that track and access metadata—the information about database objects. These objects include system tables, system stored procedures, and information schema views.

Before version 7 was available, getting to this metadata meant creating complex routines to query the system tables and decode bitmask fields. But now there are three property functions for querying the attributes of database objects: OBJECTPROPERTY, COLUMNPROPERTY, and DATABASEPROPERTY. In this 10-Minute Solution, I take a look at the OBJECTPROPERTY function and describe the kind of data you can retrieve with it.

The OBJECTPROPERTY function takes two parameters: the object ID and the property name. In most cases this function returns either a 1 (True) or a 0 (False), except in some cases where an object ID is returned. Here is the syntax of this function:



OBJECTPROPERTY(ObjectID, PropertyName)

Every object created in a SQL Server database has a unique ID, all of which are stored in the sysobjects system table. The first input parameter of this function is an integer containing the unique ID of the object you want to find out about. The PropertyName input parameter is the string value of the property you want to query. This code returns whether the authors trigger in the Pubs database has a primary key:

SELECT OBJECTPROPERTY(OBJECT_ID('authors'), 'TableHasPrimaryKey')

You most likely won't know the ID of the object you want to know about but you'll know at least its name. Therefore, you can use the OBJECT_ID function, which returns the unique ID for a given object name. If you pass the OBJECTPROPERTY function an invalid object ID or property name, it returns a NULL but it won't generate an error.

What kind of information can you find out with OBJECTPROPERTY? Anything from whether an object is a table (IsTable) to whether a table has an insert trigger (TableHasInsertTrigger). Over 30 of the 50 properties query various table attributes. You can find out if a table has an index, a foreign key, a primary key, a timestamp column, and so on. See Table 1 for the full list. (All property names return 0 or 1, except where noted.) You can use OBJECTPROPERTY anywhere you call standard functions, such as stored procedures or triggers.

Table 1. Property Name Values Used with the OBJECTPROPERTY Function

Name

Description

CnstIsClustKey

Returns whether or not the specified constraint is a primary key with a clustered index

CnstIsColumn

Returns whether or not the constraint is a column constraint (as opposed to a table constraint)

CnstIsDisabled

Returns whether or not the constraint is disabled

CnstIsNonclustKey

Returns whether or not the constraint is a primary key with a non-clustered index

CnstIsNotRepl

Returns whether or not the constraint is defined as not enabled during replication

ExecIsAnsiNullsOn

A stored procedure does not allow null comparisons

ExecIsDeleteTrigger

Returns whether or not the trigger is a delete trigger

ExecIsInsertTrigger

Returns whether or not the trigger is an insert trigger

ExecIsQuotedIdentOn

A stored procedure accepts double quotes around identifiers and single quotes around literals

ExecIsStartup

Returns whether or not the stored procedure executes automatically when the SQL Server service starts

ExecIsTriggerDisabled

Returns whether or not the trigger is disabled

ExecIsUpdateTrigger

Returns whether or not the trigger is an update trigger

IsCheckCnst

Returns whether or not the object is a check constraint

IsConstraint

Returns whether or not the object is a constraint of some kind (primary key, check constraint, foreign key, etc.)

IsDefault

Returns whether or not the object is a bound default

IsDefaultCnst

Returns whether or not the object is a default constraint

IsExecuted

Returns whether or not the specified object can be executed as a trigger, stored procedure, or view

IsExtendedProc

Returns whether or not the specified object is an extended stored procedure

IsForeignKey

Returns whether or not the specified object is a foreign key constraint

IsMSShipped

Returns whether or not the object is a system object shipped with SQL Server; this is determined by checking whether the object's system bit has been set

IsPrimaryKey

Returns whether or not the object is a primary key constraint

IsProcedure

Returns whether or not the object is a stored procedure (returns False if the object is a trigger)

IsReplProc

Returns whether or not the stored procedure is a replication procedure

IsRule

Returns whether or not the object is a database rule

IsSystemTable

Returns whether or not the object is a system table

IsTable

Returns whether or not the specified object is a table

IsTrigger

Returns whether or not the object is a trigger

IsUniqueCnst

Returns whether or not the object is a unique constraint

IsUserTable

Returns whether or not the object is a user-defined table

IsView

Returns whether or not the object is a view

OwnerId

Returns the ID of the object owner

TableDeleteTrigger

Returns the object ID of the table's delete trigger

TableDeleteTriggerCount

Returns the number of delete triggers associated with a table

TableFulltextCatalogId

Returns the object ID of the catalog that contains the full-text indexes for a table

TableHasActiveFulltextIndex

Returns whether or not the table contains a full-text index

TableHasCheckCnst

Returns whether or not the table has at least one check constraint

TableHasClustIndex

Returns whether or not the table has a clustered index

TableHasDefaultCnst

Returns whether or not the table has at least one default constraint

TableHasDeleteTrigger

Returns whether or not the table has a delete trigger

TableHasForeignKey

Returns whether or not the table has at least one foreign key

TableHasIdentity

Returns whether or not the table contains a column of type Identity

TableHasIndex

Returns whether or not the table has at least one index (returns true if the table has a primary key)

TableHasInsertTrigger

Returns whether or not the table has an insert trigger

TableHasNonclustIndex

Returns whether or not the table has at least one non-clustered index

TableHasPrimaryKey

Returns whether or not the table has a primary key

TableHasRowGuidCol

Returns whether or not the table has a column defined as a RowGUID column

TableHasTextImage

Returns whether or not the table contains a column of datatype text

TableHasTimestamp

Returns whether or not the table has a timestamp column

TableHasUniqueCnst

Returns whether or not the table has a unique constraint

TableHasUpdateTrigger

Returns whether or not the table has an update trigger

TableInsertTrigger

Returns the object ID of the table's insert trigger

TableInsertTriggerCount

Returns the number of insert triggers associated with a table

TableIsFake

Returns whether or not the table is not a base table but created internally by SQL Server

TableIsPinned

Returns whether or not the table is pinned and maintained in memory

TableUpdateTrigger

Returns the object ID of the table's update trigger

TableUpdateTriggerCount

Returns the number of update triggers associated with a table

TriggerDeleteOrder

Returns the order in which this delete trigger fires

TriggerInsertOrder

Returns the order in which this insert trigger fires

TriggerUpdateOrder

Returns the order in which this update trigger fires

Although you can return object IDs for objects in other databases by specifying the database—

SELECT OBJECT_ID('Northwind..Categories')

—the OBJECTPROPERTY function doesn't work across databases so you must always be sure you're using the correct database.

Stay tuned for my next 10-Minute Solution, where I'll look at the column and database information you can query with the other property functions.



   
Dianne Siebold is a consultant specializing in Visual Basic and SQL Server programming. She is also the author of the "Visual Basic Developer's Guide to SQL Server" (Sybex). Reach her by e-mail here.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date