IF Object_id('dbo.DropObject') IS NOT NULL
DROP PROCEDURE dbo.dropobject;
go
Procedure dbo.DropObject handles checking to see if the object exists, and also creates the proper statement to drop the object based on the object's type.The procedure's signature is thus:
CREATE PROCEDURE dbo.Dropobject (@object NVARCHAR(400),The object is found using built-in Object_id. If needed to disambiguate, the type can be specified as defined in the 'type' column of sys.objects. If foreign key constraints are preventing a table drop, they will be listed. This leads to helpful function #2:
@type SYSNAME = '')
AS
CREATE PROCEDURE dbo.Dropreferingfks(@table NVARCHAR(256))
As expected, this function will remove any foreign keys referencing a table, which will allow for the table to be dropped.
IF Object_id('dbo.DropObject') IS NOT NULL
DROP PROCEDURE dbo.dropobject;
go
CREATE PROCEDURE dbo.Dropobject (@object NVARCHAR(400),
@type SYSNAME = '')
AS
BEGIN
DECLARE @qschema SYSNAME,
@qname SYSNAME;
DECLARE @schema SYSNAME,
@name SYSNAME;
DECLARE @id INT;
IF @type = ''
SET @id = Object_id(@object)
ELSE
SET @id = Object_id(@object, @type)
IF @id IS NOT NULL
SELECT @qname = Quotename(sys.objects.name),
@qschema = Quotename(sys.schemas.name),
@name = sys.objects.name,
@schema = sys.schemas.name,
@type = sys.objects.type
FROM sys.objects
INNER JOIN sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.object_id = @id
AND ( @type = ''
OR @type = sys.objects.type );
IF @id IS NULL
BEGIN
SELECT @id = object_id,
@qname = Quotename(name),
@type = 'TD'
FROM sys.triggers
WHERE name = @object
AND parent_class = 0
AND @type IN ( '', 'TD' );
END
IF @id IS NULL
BEGIN
EXEC dbo.Log
'dbo',
'DropObject',
'W',
'Did not find object "',
@object,
'" of type "',
@type,
'"';
RETURN;
END
IF @qname IS NULL
BEGIN
EXEC dbo.Log
'dbo',
'DropObject',
'W',
'Found id ',
@id,
' but no object';
RETURN;
END
EXEC dbo.Log
'dbo',
'DropObject',
'V',
'Found ',
@qschema,
'.',
@qname,
' type ',
@type;
DECLARE @sql NVARCHAR(max);
IF @type = 'D '
SET @sql = '';
IF @type = 'F '
SELECT @sql = 'alter table '
+ Quotename(ctu.table_schema) + '.'
+ Quotename(ctu.table_name)
+ ' drop constraint '
+ Quotename(ctu.constraint_name)
FROM information_schema.constraint_table_usage AS ctu
WHERE constraint_name = @name
IF @type = 'FN'
SET @sql = 'drop function ' + @qschema + '.' + @qname
IF @type = 'FS'
SET @sql = 'drop function ' + @qschema + '.' + @qname
IF @type = 'IF'
SET @sql = '';
IF @type = 'IT'
SET @sql = '';
IF @type = 'P '
SET @sql = 'drop procedure ' + @qschema + '.' + @qname
IF @type = 'PC'
SET @sql = '';
IF @type = 'PK'
SET @sql = '';
IF @type = 'S '
SET @sql = '';
IF @type = 'SQ'
SET @sql = '';
IF @type = 'TD'
SET @sql = 'drop TRIGGER ' + @qname + ' ON DATABASE';
IF @type = 'TF'
SET @sql = 'drop function ' + @qschema + '.' + @qname;
IF @type = 'TR'
SET @sql = 'drop trigger ' + @qschema + '.' + @qname
IF @type = 'U '
BEGIN
--check for foreign key relationships to table
DECLARE @ot NVARCHAR(max);
SELECT
--pk.TABLE_SCHEMA as pkTableSchema, pk.TABLE_NAME as pkTableName, fk.TABLE_SCHEMA as pkTableSchema, fk.TABLE_NAME as pkTableName
@ot = Isnull(@ot + ', ' + Quotename(fk.table_schema) + '.'
+ Quotename(fk.table_name),
Quotename(fk.table_schema) + '.'
+ Quotename(fk.table_name))
FROM information_schema.referential_constraints AS rc
INNER JOIN information_schema.constraint_table_usage AS pk
ON pk.constraint_schema = rc.unique_constraint_schema
AND pk.constraint_name = rc.unique_constraint_name
INNER JOIN information_schema.constraint_table_usage AS fk
ON fk.constraint_schema = rc.constraint_schema
AND fk.constraint_name = rc.constraint_name
WHERE pk.table_schema = @schema
AND pk.table_name = @name;
IF @ot IS NOT NULL
BEGIN
SET @ot = @qschema + '.' + @qname
+ ' is referenced by table(s) ' + @ot;
EXEC dbo.Log
'dbo',
'DropObject',
'E',
'Drop fails; ',
@ot;
RETURN;
END
SET @sql = 'drop table ' + @qschema + '.' + @qname
END
IF @type = 'UQ'
SET @sql = '';
IF @type = 'V '
SET @sql = 'drop view ' + @qschema + '.' + @qname
IF @sql = ''
BEGIN
EXEC dbo.Log
'dbo',
'DropObject',
'W',
'No drop command defined for ',
@type;
RETURN;
END
EXEC dbo.Log
'dbo',
'DropObject',
'V',
'Executing "',
@sql,
'"';
EXEC Sp_executesql
@statement = @sql;
EXEC dbo.Log
'dbo',
'DropObject',
'I',
'Dropped ',
@qschema,
'.',
@qname,
' type ',
@type;
END
go
EXEC Dropobject
'dbo.dropReferingFKs';
go
CREATE PROCEDURE dbo.Dropreferingfks(@table NVARCHAR(256))
AS
BEGIN
DECLARE @ot NVARCHAR(max) = ''
DECLARE @schema SYSNAME,
@name SYSNAME;
DECLARE @id INT,
@cnt INT;
SET @id = Object_id(@table, 'U')
IF @id IS NULL
BEGIN
EXEC dbo.Log
'dbo',
'dropReferingFKs',
'W',
'Did not find user table "',
@table;
RETURN;
END
SELECT @name = sys.objects.name,
@schema = sys.schemas.name
FROM sys.objects
INNER JOIN sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.object_id = @id;
SELECT
--pk.TABLE_SCHEMA as pkTableSchema, pk.TABLE_NAME as pkTableName, fk.TABLE_SCHEMA as fkTableSchema, fk.TABLE_NAME as fkTableName
@ot = @ot + 'exec dropObject '
+ Quotename(Quotename(rc.constraint_name), '''')
+ '; '
FROM information_schema.referential_constraints AS rc
INNER JOIN information_schema.constraint_table_usage AS pk
ON pk.constraint_schema = rc.unique_constraint_schema
AND pk.constraint_name = rc.unique_constraint_name
INNER JOIN information_schema.constraint_table_usage AS fk
ON fk.constraint_schema = rc.constraint_schema
AND fk.constraint_name = rc.constraint_name
WHERE pk.table_schema = @schema
AND pk.table_name = @name;
SET @cnt = @@ROWCOUNT;
IF @cnt = 0
BEGIN
EXEC dbo.Log
'dbo',
'dropReferingFKs',
'V',
'Did not find any foreign keys referencing ',
@schema,
'.',
@name;
RETURN;
END
EXEC dbo.Log
'dbo',
'dropReferingFKs',
'I',
'Dropping ',
@cnt,
' foreign key(s) referencing ',
@schema,
'.',
@name;
EXEC dbo.Log
'dbo',
'dropReferingFKs',
'V',
'Preparing to sp_exec: ',
@ot;
EXEC Sp_executesql
@ot;
END
go
No comments:
Post a Comment