?????????????????????????????????????????????????????????????????????????????????????????????????????????????SQL??????????????????á?
??????????????????????????????????????????????????????????α????????SELECT?????????ó????????￡????????????????????????????????????????????????
--????????????????????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 =????洢????