Blog Pages

Make Drop statements for table deletion

-----------------------------------------------------------------------
-- execute as SP:
CREATE PROCEDURE  [dbo].[Itai_MakeStatementsForDropTable] 
@TableName nvarchar(250),
@SchemaName nvarchar(50),
@ShowDetailes bit = 1
AS
-----------------------------------------------------------------------
-- execute as script:
--declare @TableName nvarchar(250), @SchemaName nvarchar(50), @ShowDetailes bit
--select @TableName = 'KPI_InternalTypes', @SchemaName = 'dbo', @ShowDetailes = 1
-----------------------------------------------------------------------

-- 1. table Constraints
SELECT 'table Constraints' as msg, OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
into #Temp 
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
and OBJECT_NAME(parent_object_id) = @TableName and SCHEMA_NAME(schema_id) = @SchemaName

-- 2. foreign keys Constraints from other tables
select  'foreign keys constraints' as msg,
object_name(f.constraint_object_id) as NameofConstraint,
SCHEMA_NAME(o.schema_id) AS SchemaName,
object_name(f.parent_object_id) AS TableName,
type_desc AS ConstraintType
into #Temp2
from sys.foreign_key_columns f
inner join sys.objects o on o.object_id = f.constraint_object_id  and SCHEMA_NAME(o.schema_id) = @SchemaName
where object_name(f.referenced_object_id) = @TableName


if @ShowDetailes = 1
begin
select * from #Temp
union all
select * from #Temp2
end

-- 1. table Constraints
select msg, 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] DROP CONSTRAINT [' + NameofConstraint + ']' as Statement_
from #Temp2
UNION all
-- 2. foreign keys Constraints from other tables
select msg, 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] DROP CONSTRAINT [' + NameofConstraint + ']' as Statement_
from #Temp
UNION all
-- 3. drop statement for the table
select 'table', 'DROP TABLE  [' + @SchemaName + '].[' + @TableName + ']' as Statement_

drop table #Temp
drop table #Temp2
go

No comments:

Post a Comment