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.
