SQL SERVER Object Property

SQL SERVER Object Property

Object properties that i have used frequently. Most of the times i have used object properties is to check whether the object exists or not. For example when deploying an existing procedure to production, the IsProcedure property is handy to check if the procedure exists in production or not. IsScalarFunction can be used to check existence of a function.

If it exists rename it with appropriate date time stamp (never use time delimiter i.e. 12:35:58 in an object name as it can cause problems if you are planning to use SQL Server Replication in future as these object names cannot be handled by the replication agent) and then you can successfully create a fresh copy of the modified procedure and you will have a backup copy as well.

Similarly try using all object properties in your spare time and think of ways  they can be used to make your life at work simple, you only need to be aware of what object properties exist and not remember the syntax (at least i don't) 

OBJECTPROPERTY ( id , property )

IsProcedure
IsScalarFunction
CnstIsClustKey
CnstIsColumn
CnstIsNonclustKey
ExecIsTriggerNotForRepl
IsCheckCnst
IsConstraint
IsForeignKey
IsIndexed
IsPrimaryKey
IsTable

GOOGLE: "sql server msdn objectproperty"

Update: For checking existance of non unique and non clustered indexes i did not find any ObjectProperty. 

USE [myDB]
GO


IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = 'idx_account_id' AND id = OBJECT_ID ('myDB.dbo.account'))
BEGIN
  CREATE NONCLUSTERED INDEX [ idx_account_id ] ON [dbo].[account] 
  (
  [UCN] ASC
  ) ON [PRIMARY]
END

The 2 ObjectPorperties below will not work for non unique and non clustered indexes.

Property name	Object type	Description and values returned
CnstIsClustKey	Constraint	PRIMARY KEY constraint with a clustered index. 1 = True 0 = False

CnstIsNonclustKey

Constraint

PRIMARY KEY or UNIQUE constraint with a nonclustered index. 1 = True 0 = False