System Stored Procedures

Using the “sp_” prefix for a stored procedure has some interesting effects on SQL Server.  Firstly it looks for the stored procedure in the Master database (dbo schema) and then it looks in the current database (default schema for the user, then dbo schema).

The reason it is looking in Master first is that it assumes that any stored procedure starting sp_ is a system procedure, provided by Microsoft.  The system procedures have some interesting attributes that can make them very useful:

  1. The execution of dynamic SQL will have the context of the database that the stored procedure is being run from.
  2. The execution of static SQL will have the context of the Master database

When the system procedures are created during setup, an undocumented stored procedure is used to flag them as system procedures:

sp_MS_marksystemobject

This procedure changes the behaviour of the static SQL within a procedure so that it too executes in the context of the database from which it is being executed.

As always with undocumented stored procedures, Microsoft does not support their use and reserves the right to change or remove them without notice so they are to be used with extreme caution.

I have a suite of stored procedures that I create in the master database and flag as system procedures so that they can be used in the scripts that I use to make changes to database objects.  Having them in a central location and accessible from all databases within an instance makes these procedures very useful.

The procedures that I have created are mostly used to remove objects that may prevent the dropping of an existing object, e.g. to remove foreign keys that reference a primary key or removing stored procedures that reference a table type.

Common Factors

All of my system procedures are prefixed sp_dba_ so that they are easily identifiable.

They all have an input parameter @aSPMode which defaults to ‘0’ indicating that  no database objects should be dropped, instead details of them should be returned.

All of the table related procedures have an input parameter @aTableName that accepts the name of a table.  The table type procedure has an input parameter @aTableTypeName that accepts the name of the table type.

Some of the procedures have an input parameter @aDirection that is either ‘in’ or ‘out’ indicating the direction of reference of the objects, e.g. all foreign keys referencing a table is ‘in’ and all foreign keys stemming from a table is ‘out’.

sp_dba_Table_DropFK

This procedure is used to drop foreign keys referencing an object associated with a table.  The objects include that may be referenced include primary keys and unique constraints.

1 /************************************************** 2 Name: master.dbo.sp_dba_Table_DropFK 3 Written by: Al Eardley 4 Date: December 2010 5 Purpose: 6 This script drops all of the foreign keys related to a table 7 8 Parameters: 9 @aSPMode 10 0 - prints details of foreign keys relating to a table - DEFAULT 11 1 - drops details of foreign keys relating to a table 12 @aDirection - NVARCHAR(3) 13 'in' - deals with foreign keys referencing a table - DEFAULT 14 'out' - deals with foreign keys from a table 15 else - error reported 16 @aTableName - SYSNAME 17 Name of the table 18 19 Execution: 20 DECLARE @rv int 21 EXEC @rv = sp_dba_Table_DropFK @aSPMode = 1, @aDirection = 'in', @aTableName = 'Customer' 22 PRINT @rv 23 24 Please note that this SP does not require qualification as it starts sp_ 25 **************************************************/ 26 27 /**************************************************/ 28 PRINT 'master.dbo.sp_dba_Table_DropFK'; 29 /**************************************************/ 30 SET ANSI_NULLS ON; 31 SET NOCOUNT ON; 32 33 IF DB_NAME() != 'master' 34 BEGIN 35 /**************************************************/ 36 PRINT CHAR(9) + 'changing context to master'; 37 /**************************************************/ 38 USE master; 39 END 40 41 IF OBJECT_ID('dbo.sp_dba_Table_DropFK') IS NOT NULL 42 BEGIN 43 /**************************************************/ 44 PRINT CHAR(9) + 'dropping'; 45 /**************************************************/ 46 DROP PROCEDURE dbo.sp_dba_Table_DropFK; 47 END 48 49 /**************************************************/ 50 PRINT CHAR(9) + 'creating'; 51 /**************************************************/ 52 GO 53 54 CREATE PROCEDURE dbo.sp_dba_Table_DropFK ( 55 @aSPMode INT = 0 56 , @aDirection NVARCHAR(3) = 'IN' 57 , @aTableName SYSNAME = '' 58 ) AS 59 60 SET NOCOUNT ON; 61 62 /************************************************** 63 Declare default variables 64 **************************************************/ 65 DECLARE 66 @CalledSPReturnValue INT = 0 67 , @CodeBlockDesc NVARCHAR(256) = '' 68 , @ErrorID INT = 0 69 , @ErrorDesc NVARCHAR(256) = 'No Error' 70 , @ReturnValue INT = 0 71 , @Rowcount INT = 0 72 , @SPName NVARCHAR(128) = 'master.dbo.sp_dba_Table_DropFK' 73 ; 74 75 /************************************************** 76 Declare local variables 77 **************************************************/ 78 DECLARE 79 @SQLCommand NVARCHAR(1024) 80 ; 81 82 /************************************************** 83 Validate input parameters 84 **************************************************/ 85 IF DB_NAME() IN ('master', 'model', 'msdb', 'Resource', 'tempdb', 'distribution') 86 BEGIN 87 PRINT DB_NAME() + ' is a system database. You cannot drop all tables using this procedure. Exiting procedure'; 88 RETURN -1; 89 END 90 91 IF @aSPMode NOT IN (0,1) 92 BEGIN 93 PRINT '@aSPMode has not been set to 0 (display) or 1 (drop) therefore it is being defaulted to 0. No database objects will be dropped'; 94 SET @aSPMode = 0; 95 END 96 97 IF @aDirection != 'in' AND @aDirection != 'out' 98 BEGIN 99 PRINT '@aDirection has not been set to in or out. Exiting procedure'; 100 RETURN -1; 101 END 102 103 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'IsUserTable'), -1) != 1 104 BEGIN 105 PRINT '@aTableName (' + ISNULL(@aTableName, 'NULL')+ ') is either null or not a user table. Exiting procedure'; 106 RETURN -1; 107 END 108 109 IF @aDirection = 'out' 110 BEGIN 111 /************************************************** 112 DirectionOut: 113 Check if the table has any foreign keys 114 If it does then delete them 115 **************************************************/ 116 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'TableHasForeignKey'), -1) = 1 117 BEGIN 118 IF @aSPMode = 0 119 BEGIN 120 /************************************************** 121 If 'display' then simply select the names of the foreign keys 122 **************************************************/ 123 PRINT 'Returning FK Names'; 124 125 SELECT 126 OBJECT_NAME(FK.OBJECT_ID) AS 'Foreign Key', 127 OBJECT_NAME(FK.parent_object_id) AS 'Parent Table', 128 OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table', 129 i.name AS 'Referenced Index' 130 FROM sys.foreign_keys FK 131 JOIN sys.indexes I ON (I.object_id = FK.referenced_object_id AND I.index_id = FK.key_index_id) 132 WHERE 133 FK.parent_object_id = OBJECT_ID(@aTableName) 134 ; 135 END 136 ELSE 137 BEGIN 138 /************************************************** 139 If 'drop' then simply drop each foriegn key 140 **************************************************/ 141 WHILE EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(@aTableName)) 142 BEGIN 143 SELECT TOP 1 144 @SQLCommand = 'ALTER TABLE ' + SCHEMA_NAME(FK.schema_id) + '.[' + OBJECT_NAME(FK.parent_object_id) + '] DROP CONSTRAINT [' + FK.name + ']' 145 FROM sys.foreign_keys FK 146 WHERE 147 FK.parent_object_id = OBJECT_ID(@aTableName) 148 ; 149 150 EXEC sp_sqlexec @SQLCommand; 151 END 152 END 153 END 154 ELSE 155 BEGIN 156 IF @aSPMode = 0 157 BEGIN 158 PRINT 'No Foreign Keys on ' + @aTableName + '. Exiting procedure'; 159 RETURN 1; 160 END 161 END 162 END 163 ELSE 164 BEGIN 165 /************************************************** 166 DirectionIn: 167 Check if the table is referenced by any foreign keys 168 If it is then delete them 169 **************************************************/ 170 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'TableHasForeignRef'), -1) = 1 171 BEGIN 172 IF @aSPMode = 0 173 BEGIN 174 /************************************************** 175 If 'display' then simply select the names of the foreign keys 176 **************************************************/ 177 SELECT 178 OBJECT_NAME(FK.OBJECT_ID) AS 'Foreign Key', 179 OBJECT_NAME(FK.parent_object_id) AS 'Parent Table', 180 OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table', 181 i.name AS 'Referenced Index' 182 FROM sys.foreign_keys FK 183 JOIN sys.indexes I ON (I.object_id = FK.referenced_object_id AND I.index_id = FK.key_index_id) 184 WHERE 185 FK.referenced_object_id = OBJECT_ID(@aTableName) 186 ; 187 END 188 ELSE 189 BEGIN 190 /************************************************** 191 If 'drop' then simply drop each foriegn key 192 **************************************************/ 193 WHILE EXISTS (SELECT * FROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID(@aTableName)) 194 BEGIN 195 SELECT TOP 1 196 @SQLCommand = 'ALTER TABLE ' + SCHEMA_NAME(FK.schema_id) + '.[' + OBJECT_NAME(FK.parent_object_id) + '] DROP CONSTRAINT [' + FK.name + ']' 197 FROM sys.foreign_keys FK 198 WHERE 199 FK.referenced_object_id = OBJECT_ID(@aTableName) 200 ; 201 202 EXEC sp_sqlexec @SQLCommand; 203 END 204 END 205 END 206 ELSE 207 BEGIN 208 IF @aSPMode = 0 209 BEGIN 210 PRINT 'No Foreign Keys reference ' + @aTableName; 211 END 212 END 213 END 214 215 RETURN 0 216 217 /************************************************** 218 ** Granting permissions 219 ** 220 ** DO NOT NEED TO GRANT PERMISSIONS ON SYSTEM PROCEDURES 221 **************************************************/ 222 223 GO 224 225 PRINT CHAR(9) + 'Marking as a system procedure'; 226 EXEC sp_MS_marksystemobject 'dbo.sp_dba_Table_DropFK'; 227

sp_dba_Table_DropPK

This procedure is used to drop all foreign keys referencing a primary key for a given table.

1 /************************************************** 2 Name: master.dbo.sp_dba_Table_DropPK 3 Written by: Al Eardley 4 Date: December 2010 5 Purpose: 6 This script drops all of the foreign keys referencing the primary key of a table 7 8 Parameters: 9 @aSPMode 10 0 - prints details of foreign keys relating to a table - DEFAULT 11 1 - drops details of foreign keys relating to a table 12 @aTableName - SYSNAME 13 Name of the table 14 15 Execution: 16 DECLARE @rv int 17 EXEC @rv = sp_dba_Table_DropPK @aSPMode = 1, @aTableName = 'Customer' 18 PRINT @rv 19 20 Please note that this SP does not require qualification as it starts sp_ 21 **************************************************/ 22 23 /**************************************************/ 24 PRINT 'master.dbo.sp_dba_Table_DropPK'; 25 /**************************************************/ 26 SET ANSI_NULLS ON; 27 SET NOCOUNT ON; 28 29 IF DB_NAME() != 'master' 30 BEGIN 31 /**************************************************/ 32 PRINT CHAR(9) + 'changing context to master'; 33 /**************************************************/ 34 USE master; 35 END 36 37 IF OBJECT_ID('dbo.sp_dba_Table_DropPK') IS NOT NULL 38 BEGIN 39 /**************************************************/ 40 PRINT CHAR(9) + 'dropping'; 41 /**************************************************/ 42 DROP PROCEDURE dbo.sp_dba_Table_DropPK; 43 END 44 45 /**************************************************/ 46 PRINT CHAR(9) + 'creating'; 47 /**************************************************/ 48 GO 49 50 CREATE PROCEDURE dbo.sp_dba_Table_DropPK ( 51 @aSPMode INT = 0 52 , @aTableName SYSNAME = '' 53 ) AS 54 55 SET NOCOUNT ON; 56 57 /************************************************** 58 Declare default variables 59 **************************************************/ 60 DECLARE 61 @CalledSPReturnValue INT = 0 62 , @CodeBlockDesc NVARCHAR(256) = '' 63 , @ErrorID INT = 0 64 , @ErrorDesc NVARCHAR(256) = 'No Error' 65 , @ReturnValue INT = 0 66 , @Rowcount INT = 0 67 , @SPName NVARCHAR(128) = 'master.dbo.sp_dba_Table_DropPK' 68 ; 69 70 /************************************************** 71 Declare local variables 72 **************************************************/ 73 DECLARE 74 @SQLCommand NVARCHAR(1024) 75 ; 76 77 /************************************************** 78 Validate input parameters 79 **************************************************/ 80 IF DB_NAME() IN ('master', 'model', 'msdb', 'Resource', 'tempdb', 'distribution') 81 BEGIN 82 PRINT DB_NAME() + ' is a system database. You cannot drop all tables using this procedure. Exiting procedure'; 83 RETURN -1; 84 END 85 86 IF @aSPMode NOT IN (0,1) 87 BEGIN 88 PRINT '@aSPMode has not been set to 0 (display) or 1 (drop) therefore it is being defaulted to 0. No database objects will be dropped'; 89 SET @aSPMode = 0; 90 END 91 92 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'IsUserTable'), -1) != 1 93 BEGIN 94 PRINT '@aTableName (' + ISNULL(@aTableName, 'NULL')+ ') is either null or not a user table. Exiting procedure'; 95 RETURN -1; 96 END 97 98 99 /************************************************** 100 DirectionIn: 101 Check if the primary key on the table is referenced by any foreign keys 102 If it is then delete them 103 **************************************************/ 104 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'TableHasForeignRef'), -1) = 1 105 BEGIN 106 IF @aSPMode = 0 107 BEGIN 108 /************************************************** 109 If 'display' then simply select the names of the foreign keys 110 **************************************************/ 111 SELECT 112 OBJECT_NAME(FK.OBJECT_ID) AS 'Foreign Key' 113 , SCHEMA_NAME(FK.schema_id) + '.' + OBJECT_NAME(FK.parent_object_id) AS 'Parent Table' 114 , SCHEMA_NAME(FK.schema_id) + '.' + OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table' 115 , i.name AS 'Referenced Index' 116 FROM sys.foreign_keys FK 117 JOIN sys.indexes I ON (I.object_id = FK.referenced_object_id AND I.index_id = FK.key_index_id AND i.is_primary_key = 1) 118 WHERE 119 FK.referenced_object_id = OBJECT_ID(SCHEMA_NAME(FK.schema_id) + '.' + @aTableName) 120 ; 121 END 122 ELSE 123 BEGIN 124 /************************************************** 125 If 'drop' then simply drop each foriegn key 126 **************************************************/ 127 WHILE EXISTS (SELECT FK.* FROM sys.foreign_keys FK 128 JOIN sys.indexes I ON (I.object_id = FK.referenced_object_id AND I.index_id = FK.key_index_id AND i.is_primary_key = 1) 129 WHERE referenced_object_id = OBJECT_ID(@aTableName)) 130 BEGIN 131 SELECT TOP 1 132 @SQLCommand = 'ALTER TABLE ' + SCHEMA_NAME(FK.schema_id) + '.' + OBJECT_NAME(FK.parent_object_id) + ' DROP CONSTRAINT ' + FK.name 133 FROM sys.foreign_keys FK 134 WHERE 135 FK.referenced_object_id = OBJECT_ID(@aTableName) 136 ; 137 138 EXEC sp_sqlexec @SQLCommand; 139 END 140 END 141 END 142 ELSE 143 BEGIN 144 IF @aSPMode = 0 145 BEGIN 146 PRINT 'No Foreign Keys reference ' + @aTableName; 147 END 148 END 149 150 151 RETURN 0 152 153 /************************************************** 154 ** Granting permissions 155 ** 156 ** DO NOT NEED TO GRANT PERMISSIONS ON SYSTEM PROCEDURES 157 **************************************************/ 158 159 GO 160 161 PRINT CHAR(9) + 'Marking as a system procedure'; 162 EXEC sp_MS_marksystemobject 'dbo.sp_dba_Table_DropPK'; 163

sp_dba_Table_DropUC

This procedure is used to drop all foreign keys referencing the unique constraint for a given table.

1 /************************************************** 2 Name: master.dbo.sp_dba_Table_DropUC 3 Written by: Al Eardley 4 Date: December 2010 5 Purpose: 6 This script drops all of the foreign keys referencing the unique constraint on a table 7 8 Parameters: 9 @aSPMode 10 0 - prints details of foreign keys relating to a table - DEFAULT 11 1 - drops details of foreign keys relating to a table 12 @aTableName - SYSNAME 13 Name of the table 14 15 Execution: 16 DECLARE @rv int 17 EXEC @rv = sp_dba_Table_DropUC @aSPMode = 1, @aTableName = 'Customer' 18 PRINT @rv 19 20 Please note that this SP does not require qualification as it starts sp_ 21 **************************************************/ 22 23 /**************************************************/ 24 PRINT 'master.dbo.sp_dba_Table_DropUC'; 25 /**************************************************/ 26 SET ANSI_NULLS ON; 27 SET NOCOUNT ON; 28 29 IF DB_NAME() != 'master' 30 BEGIN 31 /**************************************************/ 32 PRINT CHAR(9) + 'changing context to master'; 33 /**************************************************/ 34 USE master; 35 END 36 37 IF OBJECT_ID('dbo.sp_dba_Table_DropUC') IS NOT NULL 38 BEGIN 39 /**************************************************/ 40 PRINT CHAR(9) + 'dropping'; 41 /**************************************************/ 42 DROP PROCEDURE dbo.sp_dba_Table_DropUC; 43 END 44 45 /**************************************************/ 46 PRINT CHAR(9) + 'creating'; 47 /**************************************************/ 48 GO 49 50 CREATE PROCEDURE dbo.sp_dba_Table_DropUC ( 51 @aSPMode INT = 0 52 , @aTableName SYSNAME = '' 53 ) AS 54 55 SET NOCOUNT ON; 56 57 /************************************************** 58 Declare default variables 59 **************************************************/ 60 DECLARE 61 @CalledSPReturnValue INT = 0 62 , @CodeBlockDesc NVARCHAR(256) = '' 63 , @ErrorID INT = 0 64 , @ErrorDesc NVARCHAR(256) = 'No Error' 65 , @ReturnValue INT = 0 66 , @Rowcount INT = 0 67 , @SPName NVARCHAR(128) = 'master.dbo.sp_dba_Table_DropUC' 68 ; 69 70 /************************************************** 71 Declare local variables 72 **************************************************/ 73 DECLARE 74 @SQLCommand NVARCHAR(1024) 75 ; 76 77 /************************************************** 78 Validate input parameters 79 **************************************************/ 80 IF DB_NAME() IN ('master', 'model', 'msdb', 'Resource', 'tempdb', 'distribution') 81 BEGIN 82 PRINT DB_NAME() + ' is a system database. You cannot drop all tables using this procedure. Exiting procedure'; 83 RETURN -1; 84 END 85 86 IF @aSPMode NOT IN (0,1) 87 BEGIN 88 PRINT '@aSPMode has not been set to 0 (display) or 1 (drop) therefore it is being defaulted to 0. No database objects will be dropped'; 89 SET @aSPMode = 0; 90 END 91 92 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'IsUserTable'), -1) != 1 93 BEGIN 94 PRINT '@aTableName (' + ISNULL(@aTableName, 'NULL')+ ') is either null or not a user table. Exiting procedure'; 95 RETURN -1; 96 END 97 98 99 /************************************************** 100 DirectionIn: 101 Check if the primary key on the table is referenced by any foreign keys 102 If it is then delete them 103 **************************************************/ 104 IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@aTableName), N'TableHasForeignRef'), -1) = 1 105 BEGIN 106 IF @aSPMode = 0 107 BEGIN 108 /************************************************** 109 If 'display' then simply select the names of the foreign keys 110 **************************************************/ 111 SELECT 112 OBJECT_NAME(FK.OBJECT_ID) AS 'Foreign Key' 113 , SCHEMA_NAME(FK.schema_id) + '.' + OBJECT_NAME(FK.parent_object_id) AS 'Parent Table' 114 , SCHEMA_NAME(FK.schema_id) + '.' + OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table' 115 , i.name AS 'Referenced Index' 116 FROM sys.foreign_keys FK 117 JOIN sys.indexes I ON (I.object_id = FK.referenced_object_id AND I.index_id = FK.key_index_id AND i.is_unique_constraint = 1) 118 WHERE 119 FK.referenced_object_id = OBJECT_ID(SCHEMA_NAME(FK.schema_id) + '.' + @aTableName) 120 ; 121 END 122 ELSE 123 BEGIN 124 /************************************************** 125 If 'drop' then simply drop each foriegn key 126 **************************************************/ 127 WHILE EXISTS (SELECT FK.* FROM sys.foreign_keys FK 128 JOIN sys.indexes I ON (I.object_id = FK.referenced_object_id AND I.index_id = FK.key_index_id AND i.is_unique_constraint = 1) 129 WHERE referenced_object_id = OBJECT_ID(@aTableName)) 130 BEGIN 131 SELECT TOP 1 132 @SQLCommand = 'ALTER TABLE ' + SCHEMA_NAME(FK.schema_id) + '.' + OBJECT_NAME(FK.parent_object_id) + ' DROP CONSTRAINT ' + FK.name 133 FROM sys.foreign_keys FK 134 WHERE 135 FK.referenced_object_id = OBJECT_ID(@aTableName) 136 ; 137 138 EXEC sp_sqlexec @SQLCommand; 139 END 140 END 141 END 142 ELSE 143 BEGIN 144 IF @aSPMode = 0 145 BEGIN 146 PRINT 'No Foreign Keys reference ' + @aTableName; 147 END 148 END 149 150 151 RETURN 0 152 153 /************************************************** 154 ** Granting permissions 155 ** 156 ** DO NOT NEED TO GRANT PERMISSIONS ON SYSTEM PROCEDURES 157 **************************************************/ 158 159 GO 160 161 PRINT CHAR(9) + 'Marking as a system procedure'; 162 EXEC sp_MS_marksystemobject 'dbo.sp_dba_Table_DropUC'; 163

sp_dba_TableType_DropRef

This procedure drops all stored procedures that reference the table type.

1 /************************************************** 2 Name: master.dbo.sp_dba_TableType_DropRef 3 Written by: Al Eardley 4 Date: December 2010 5 Purpose: 6 This script drops all of the foreign keys related to a table 7 8 Parameters: 9 @aSPMode 10 0 - prints details of foreign keys relating to a table - DEFAULT 11 1 - drops details of foreign keys relating to a table 12 @aTableTypeName - SYSNAME 13 Name of the type 14 15 Execution: 16 DECLARE @rv int 17 EXEC @rv = sp_dba_TableType_DropRef @aSPMode = 1, @aTableTypeName = 'Customer' 18 PRINT @rv 19 20 Please note that this SP does not require qualification as it starts sp_ 21 **************************************************/ 22 23 /**************************************************/ 24 PRINT 'master.dbo.sp_dba_TableType_DropRef'; 25 /**************************************************/ 26 SET ANSI_NULLS ON; 27 SET NOCOUNT ON; 28 29 IF DB_NAME() != 'master' 30 BEGIN 31 /**************************************************/ 32 PRINT CHAR(9) + 'changing context to master'; 33 /**************************************************/ 34 USE master; 35 END 36 37 IF OBJECT_ID('dbo.sp_dba_TableType_DropRef') IS NOT NULL 38 BEGIN 39 /**************************************************/ 40 PRINT CHAR(9) + 'dropping'; 41 /**************************************************/ 42 DROP PROCEDURE dbo.sp_dba_TableType_DropRef; 43 END 44 45 /**************************************************/ 46 PRINT CHAR(9) + 'creating'; 47 /**************************************************/ 48 GO 49 50 CREATE PROCEDURE dbo.sp_dba_TableType_DropRef ( 51 @aSPMode INT = 0 52 , @aTableTypeName SYSNAME = '' 53 ) AS 54 55 SET NOCOUNT ON; 56 57 /************************************************** 58 Declare default variables 59 **************************************************/ 60 DECLARE 61 @CalledSPReturnValue INT = 0 62 , @CodeBlockDesc NVARCHAR(256) = '' 63 , @ErrorID INT = 0 64 , @ErrorDesc NVARCHAR(256) = 'No Error' 65 , @ReturnValue INT = 0 66 , @Rowcount INT = 0 67 , @SPName NVARCHAR(128) = 'master.dbo.sp_dba_TableType_DropRef' 68 ; 69 70 /************************************************** 71 Declare local variables 72 **************************************************/ 73 DECLARE 74 @SQLCommand NVARCHAR(1024) 75 ; 76 77 /************************************************** 78 Validate input parameters 79 **************************************************/ 80 IF DB_NAME() IN ('master', 'model', 'msdb', 'Resource', 'tempdb', 'distribution') 81 BEGIN 82 PRINT DB_NAME() + ' is a system database. You cannot drop all objects referencing a type using this procedure. Exiting procedure'; 83 RETURN -1; 84 END 85 86 IF @aSPMode NOT IN (0,1) 87 BEGIN 88 PRINT '@aSPMode has not been set to 0 (display) or 1 (drop) therefore it is being defaulted to 0. No database objects will be dropped'; 89 SET @aSPMode = 0; 90 END 91 92 IF NOT EXISTS (SELECT * FROM sys.table_types TT WHERE TT.name = TYPE_NAME(TYPE_ID(@aTableTypeName))) 93 BEGIN 94 PRINT '@aTableTypeName (' + ISNULL(@aTableTypeName, 'NULL')+ ') is either null or not a user table type. Exiting procedure'; 95 RETURN -1; 96 END 97 98 IF @aSPMode = 0 99 BEGIN 100 /************************************************** 101 If 'display' then simply select the names of the foreign keys 102 **************************************************/ 103 PRINT 'Returning object Names'; 104 105 SELECT 106 OBJECT_SCHEMA_NAME(P.object_id) + '.' + OBJECT_NAME(P.object_id) 107 , CASE OBJECTPROPERTYEX(P.object_id, 'BaseType') 108 WHEN 'AF' THEN 'Aggregate function (CLR)' 109 WHEN 'C' THEN 'CHECK constraint' 110 WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)' 111 WHEN 'F' THEN 'FOREIGN KEY constraint' 112 WHEN 'FN' THEN 'SQL scalar function' 113 WHEN 'FS' THEN 'Assembly (CLR) scalar-function' 114 WHEN 'FT' THEN 'Assembly (CLR) table-valued function' 115 WHEN 'IF' THEN 'SQL inline table-valued function' 116 WHEN 'IT' THEN 'Internal table' 117 WHEN 'P' THEN 'SQL Stored Procedure' 118 WHEN 'PC' THEN 'Assembly (CLR) stored-procedure' 119 WHEN 'PG' THEN 'Plan guide' 120 WHEN 'PK' THEN 'PRIMARY KEY constraint' 121 WHEN 'R' THEN 'Rule (old-style, stand-alone)' 122 WHEN 'RF' THEN 'Replication-filter-procedure' 123 WHEN 'S' THEN 'System base table' 124 WHEN 'SN' THEN 'Synonym' 125 WHEN 'SQ' THEN 'Service queue' 126 WHEN 'TA' THEN 'Assembly (CLR) DML trigger' 127 WHEN 'TF' THEN 'SQL table-valued-function' 128 WHEN 'TR' THEN 'SQL DML trigger' 129 WHEN 'TT' THEN 'Table type' 130 WHEN 'U' THEN 'Table (user-defined)' 131 WHEN 'UQ' THEN 'UNIQUE constraint' 132 WHEN 'V' THEN 'View' 133 WHEN 'X' THEN 'Extended stored procedure' 134 ELSE 'Unknown' 135 END 136 FROM sys.table_types TT 137 JOIN sys.parameters P ON (P.system_type_id = TT.system_type_id AND P.user_type_id = TT.user_type_id) 138 WHERE 139 TT.name = TYPE_NAME(TYPE_ID(@aTableTypeName)) 140 ; 141 END 142 ELSE 143 BEGIN 144 /************************************************** 145 If 'drop' then simply drop each foriegn key 146 **************************************************/ 147 WHILE EXISTS (SELECT * FROM sys.table_types TT JOIN sys.parameters P ON (P.system_type_id = TT.system_type_id AND P.user_type_id = TT.user_type_id AND OBJECTPROPERTYEX(P.object_id, 'BaseType') = 'P') WHERE TT.name = TYPE_NAME(TYPE_ID(@aTableTypeName))) 148 BEGIN 149 SELECT TOP 1 150 @SQLCommand = 'DROP PROCEDURE ' + OBJECT_SCHEMA_NAME(P.object_id) + '.' + OBJECT_NAME(P.object_id) + ';' 151 FROM sys.table_types TT 152 JOIN sys.parameters P ON (P.system_type_id = TT.system_type_id AND P.user_type_id = TT.user_type_id AND OBJECTPROPERTYEX(P.object_id, 'BaseType') = 'P') 153 WHERE 154 TT.name = TYPE_NAME(TYPE_ID(@aTableTypeName)) 155 ; 156 157 EXEC sp_sqlexec @SQLCommand; 158 END 159 END 160 161 162 RETURN 0 163 164 /************************************************** 165 ** Granting permissions 166 ** 167 ** DO NOT NEED TO GRANT PERMISSIONS ON SYSTEM PROCEDURES 168 **************************************************/ 169 170 GO 171 172 PRINT CHAR(9) + 'Marking as a system procedure'; 173 EXEC sp_MS_marksystemobject 'dbo.sp_dba_TableType_DropRef'; 174

Conclusion

These procedures form the basis of a larger suite of tools that I have in the form of system procedures.  These ones however are the procedures that I call in the scripts that I use to build database objects.

Leave a Reply

Your email address will not be published.