[轉]删除MSSQL所有的限制及表格

--删除所有限制、表、視圖等SQL腳本

--###############################################
--删除所有外鍵限制
--###############################################
DECLARE @SQL VARCHAR(99)
DECLARE CUR_CONSTRAINT CURSOR LOCAL FOR
 SELECT
  'ALTER TABLE '+
  CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
  +OBJECT_NAME(parent_object_id)+' DROP CONSTRAINT '+OBJECT_NAME(object_id)
 FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
 WHERE O.type IN('C','D','F')
OPEN CUR_CONSTRAINT
FETCH CUR_CONSTRAINT INTO @SQL
WHILE @@FETCH_STATUS =0
 BEGIN
  EXEC(@SQL)
  FETCH CUR_CONSTRAINT INTO @SQL
 END
CLOSE CUR_CONSTRAINT
DEALLOCATE CUR_CONSTRAINT

--###############################################
--删除所有視圖（存儲過程、函數等用同樣的方法）
--###############################################
--DECLARE @SQL VARCHAR(99)
DECLARE CUR_VIEW CURSOR LOCAL FOR
 SELECT
  'IF OBJECT_ID('''+
  CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
  +OBJECT_NAME(object_id)
  +''') IS NOT NULL'+
  +' DROP VIEW '+
  CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
  +OBJECT_NAME(object_id)
 FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
 WHERE O.type IN('V')
OPEN CUR_VIEW
FETCH CUR_VIEW INTO @SQL
WHILE @@FETCH_STATUS =0
 BEGIN
  EXEC(@SQL)
  FETCH CUR_VIEW INTO @SQL
 END
CLOSE CUR_VIEW
DEALLOCATE CUR_VIEW

--###############################################
-- 删除所有表
--###############################################
--DECLARE @SQL VARCHAR(99)
DECLARE CUR_TABLE CURSOR LOCAL FOR
SELECT
 'DROP TABLE '+
  CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
  +O.name
FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
WHERE O.type='U'
OPEN CUR_TABLE
FETCH CUR_TABLE INTO @SQL
WHILE @@FETCH_STATUS =0
 BEGIN
  EXEC(@SQL)
  FETCH CUR_TABLE INTO @SQL
 END
CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE

 

--###############################################

再提供一下SQL Server裡的OBJECT_ID函數object_type參數類型

--###############################################

OBJECT_ID(object_name,object_type)
對象類型：
AF =聚合函數（CLR）
C = CHECK限制
D = DEFAULT（限制或獨立）
F = FOREIGN KEY限制
FN = SQL标量函數
FS =大會（CLR）的标量函數
FT =程式集（CLR）表值函數
IF = SQL内聯表值函數
IT =内部表
P = SQL存儲過程
電腦大會（CLR）存儲過程
PG =計劃指南
PK = PRIMARY KEY限制
R =規則（舊式，單機）
RF =複制過濾過程
S =系統基表
SN =同義詞
SQ =服務隊列
TA =元件（CLR）DML觸發器
TF = SQL表值函數
TR = SQL DML觸發器
TT =表類型
U =表（使用者定義）
UQ = UNIQUE限制
V =視圖
X =擴充存儲過程
      

轉自：http://www.cnblogs.com/PongorXi/archive/2012/06/20/2556119.html

寵辱不驚，看庭前花開花落；去留無意，望天上雲卷雲舒