[MSSQL] MS SQL Transact-SQL 大量刪除約束條件Constraint 範本

-- =============================================
-- Author:		Steven玄
-- ALTER date:  20201028
-- Description:	查看 資料庫的 條件Constraint  
-- =============================================

SELECT 
	dense_rank() over(order by A.[crdate],A.[refdate],A.[id] asc) as [rk]
	,A.[name] AS [Constraint_name]   
    ,A.[id]
    ,A.[type]  
    ,A.[crdate]  
    ,A.[refdate]  
	,B.[id] as [OBJECT_ID]
FROM sysobjects AS A INNER JOIN syscolumns AS B  --sysobjects
ON A.[id] = B.[cdefault]
where A.[type] ='D'			--條件Constraint  
--AND B.[id] = OBJECT_ID('TableName') --縮小搜尋範圍資料表名稱 

--!!小心使用!!!!小心使用!!!!小心使用!!!!小心使用!!!!小心使用!!!!小心使用!!!!小心使用!!!!小心使用!!
-- =============================================
-- Author:		Steven玄
-- ALTER date:  20201028
-- Description:	產生 大量刪除 條件Constraint  的字串   (通常自串會很長)
-- =============================================
--!!Watch out!!!!Watch out!!!!Watch out!!!!Watch out!!!!Watch out!!!!Watch out!!

-- 創暫存TABLE
DECLARE @Constraint_TABLE TABLE  (
[rk] [int] NOT NULL,					--順序
[Constraint_name] [nvarchar](50) NULL,	--預存程序名稱
[id] [nvarchar](20) NULL,			--Constraint ID
[type] [nvarchar](50) NULL,			--項目類別
[crdate] [DATETIME] NULL,			--創建時間
[refdate] [DATETIME] NULL,			--修改時間
[OBJECT_ID][nvarchar](20) NULL		--物件ID
);

DECLARE @Table VARCHAR(20) = 'TAD_201' --設定要更動的資料表表的名稱


--下條件把多餘的預存程序放進去這個taBLE
INSERT INTO @Constraint_TABLE
--可先查看 
SELECT 
	dense_rank() over(order by A.[crdate],A.[refdate],A.[id] asc) as [rk]
	,A.[name] AS [Constraint_name]   
    ,A.[id]
    ,A.[type]  
    ,A.[crdate]  
    ,A.[refdate]  
	,B.[id] as [OBJECT_ID]
FROM sysobjects AS A INNER JOIN syscolumns AS B  --sysobjects
ON A.[id] = B.[cdefault]
where A.[type] ='D'			--條件Constraint  
AND B.[id] = OBJECT_ID(@Table)   --表的名稱
--設定相關條件
AND A.[name] like 'DF_TAD%'	  --Constraint 名稱




--組DROP預存程序字串
DECLARE @MAX   BIGINT;
DECLARE @Min   BIGINT;
SELECT @MAX = MAX([rk]),@Min=MIN([rk]) FROM @Constraint_TABLE

declare @string nvarchar(max)		--字串
SET  @string = 'ALTER TABLE ['+@Table+'] DROP CONSTRAINT '

DECLARE @Constraint_name nvarchar(100) --預存程序名稱
WHILE(@Min <= @MAX)
BEGIN
SELECT @Constraint_name = [Constraint_name] FROM @Constraint_TABLE  WHERE [rk] = @Min
SET @string += ' ['+@Constraint_name+'] ';
	 if @Min=@MAX SET @string += ''
	 ELSE SET @string += ',';
SET @Min=@Min+1
END

SELECT @string;--回傳字串