Saturday, March 29, 2008

Use OBJECTPROPERTY To Find Out Table Properties

How many times did you open up a table in design view to find out if a table has
a delete trigger
a clustered index
an identity column
a primary key
a timestamp
a unique constraint
etc etc?

There is an easier way to find out all this info by using the OBJECTPROPERTY function
You will have to get the object id from the table to pass it into the OBJECTPROPERTY function.
To get the object id you can use the OBJECT_ID function.
Run the code below in the Northwind database to see how it works

USE Northwind

DECLARE @objectID INT
SELECT @objectID =OBJECT_ID('customers')

select OBJECTPROPERTY(@objectID,'TableDeleteTrigger') AS 'Table Has A Delete Trigger',
OBJECTPROPERTY(@objectID,'TableHasClustIndex') AS 'Table Has A Clustered Index',
OBJECTPROPERTY(@objectID,'TableHasIdentity') AS 'Table Has An Identity Column',
OBJECTPROPERTY(@objectID,'TableHasPrimaryKey') AS 'Table Has A Primary Key',
OBJECTPROPERTY(@objectID,'TableHasTimestamp') AS 'Table Has A Timestamp',
OBJECTPROPERTY(@objectID,'TableHasUniqueCnst') AS 'Table Has A Unique Constraint',
OBJECTPROPERTY(@objectID,'IsAnsiNullsOn') AS 'ANSI NULLS Is Set To ON'

Lookup OBJECTPROPERTY in Books On Line to find out about additional properties

No comments: