--刪除所有約束、表、視圖等SQL腳本--############################################### --刪除所有外鍵約束 --############################################### DECLARE @SQL VARCHAR(99) DECLARE CUR_CONSTRAINT CURSOR LOCAL FORSELECT'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_idWHERE O.type IN('C','D','F') OPEN CUR_CONSTRAINT FETCH CUR_CONSTRAINT INTO @SQL WHILE @@FETCH_STATUS =0BEGINEXEC(@SQL)FETCH CUR_CONSTRAINT INTO @SQLEND CLOSE CUR_CONSTRAINT DEALLOCATE CUR_CONSTRAINT--############################################### --刪除所有視圖（存儲過程、函數(shù)等用同樣的方法） --############################################### --DECLARE @SQL VARCHAR(99) DECLARE CUR_VIEW CURSOR LOCAL FORSELECT'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_idWHERE O.type IN('V') OPEN CUR_VIEW FETCH CUR_VIEW INTO @SQL WHILE @@FETCH_STATUS =0BEGINEXEC(@SQL)FETCH CUR_VIEW INTO @SQLEND 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 =0BEGINEXEC(@SQL)FETCH CUR_TABLE INTO @SQLEND CLOSE CUR_TABLE DEALLOCATE CUR_TABLE--###############################################再提供一下SQL Server里的OBJECT_ID函數(shù)object_type參數(shù)類型--###############################################OBJECT_ID(object_name,object_type) 對象類型： AF =聚合函數(shù)（CLR） C = CHECK約束 D = DEFAULT（約束或獨立） F = FOREIGN KEY約束 FN = SQL標量函數(shù) FS =大會（CLR）的標量函數(shù) FT =程序集（CLR）表值函數(shù) IF = SQL內(nèi)聯(lián)表值函數(shù) IT =內(nèi)部表 P = SQL存儲過程 電腦大會（CLR）存儲過程 PG =計劃指南 PK = PRIMARY KEY約束 R =規(guī)則（舊式，單機） RF =復(fù)制過濾過程 S =系統(tǒng)基表 SN =同義詞 SQ =服務(wù)隊列 TA =組件（CLR）DML觸發(fā)器 TF = SQL表值函數(shù) TR = SQL DML觸發(fā)器 TT =表類型 U =表（用戶定義） UQ = UNIQUE約束 V =視圖 X =擴展存儲過程

?

?

轉(zhuǎn)自：http://www.cnblogs.com/PongorXi/archive/2012/06/20/2556119.html

轉(zhuǎn)載于:https://www.cnblogs.com/netWild/p/4689405.html

總結(jié)

以上是生活随笔為你收集整理的[转]删除MSSQL所有的约束及表格的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。