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