Usman ur Rehman Ahmed's blog

 

Check whether given database object exists in the DNN installation scripts

Following are the ways I have been using to check for existance of various database object types in the DNN installation/uninsatllation scripts. In each script <ObjectName> needs to be replaced with the actual database object (table, stored procedure, etc...) name

Check if Table Exists?

    IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}<TableName>]') AND type in (N'U'))

Check if Stored Procedure Exists?

    IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}<StoredProcedureName>]') AND type in (N'P', N'PC'))

Check if Function Exists?

    IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}<FunctionName>]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

Check if Foriegn Key Exists?

    IF  EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}<ForeignKeyName>]') AND parent_object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}<TableName>]'))

Filed under: DNN Database Dot Net Nuke Foreign Key Function Stored Procedure Table manifest scripts
11
To Posterous, Love Metalab