--Delete all constraints, tables, views, and other SQL scripts--###############################################--Delete all foreign KEY constraints--############################## ################ #DECLARE @SQL VARCHAR DECLARE cur_constraint CURSOR LOCAL for SELECT ' ALTER TABLE ' + case when O.sche MA_ID is isn't NULL then (SELECT name+ '. ') From Sys.schemas WHERE schema_id = o.schema_id) ELSE ' END +object_name (parent_object_id) + ' DROP CONSTRAINT ' +object_nam E (object_id) from sys.objects as O joins Sys.schemas as S on o.schema_id=s.schema_id WHERE o.type in (' C ', ' D ', ' F ') OPEN cur_c Onstraintfetch cur_constraint to @SQLWHILE @ @FETCH_STATUS =0 BEGIN EXEC (@SQL) FETCH cur_constraint into @SQL endclose Cur_constraintdeallocate cur_constraint--###############################################--Delete all views (stored procedures, function, etc in the same way)--###############################################--declare @SQL VARCHAR (DECLARE) Cur_view CURSOR LOCAL For select ' IF object_id (' "+ case when o.schema_id was not NULL and 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.schem As as S on o.schema_id=s.schema_id WHERE o.type in (' V ') OPEN cur_viewfetch cur_view into @SQLWHILE @ @FETCH_STATUS =0 BEGIN EXEC (@SQL) FETCH Cur_view into @SQL endclose cur_viewdeallocate cur_view--########################################### ####--Delete all tables--###############################################--declare @SQL VARCHAR DECLARE cur_table CURSOR LOCAL forselect ' DROP TABLE ' + case when o.schema_id was not NULL and then (SELECT name+ '. ') From Sys.schemas WHERE schema_id = o.schema_id) ELSE "END +o.namefrom sys.objects as O JOIN Sys.schemas as S on O.schem A_id=s.schema_idwhere o.type= ' U ' OPEN cur_tablefetch cur_table into @SQLWHILE @ @FETCH_STATUS =0 BEGIN EXEC (@SQL) FETCH CU R_table into @SQL endclose cur_tabledeallocate cur_table--############################################## the OBJECT_ID function object_type parameter type in #再提供一下SQL server--############################### ############### #OBJECT_ID (Object_name,object_type) object type: AF = aggregate function (CLR) C = Check Constraint D = DEFAULT (constrained or independent) F = FOREIGN key constraint fn = SQL Scalar Function FS = General Assembly (CLR) scalar function ft = assembly (CLR) table-valued function if = SQL inline table-valued function it = internal table p = SQL stored procedure PC assembly (CLR) stored Procedure PG = Plan Guide PK = PRIMARY Key constraint r = Rule (Legacy, standalone) RF = Copy filtering process s = system base table sn = synonym sq = service queue Ta = component (CLR) DML trigger TF = SQL table-valued function tr = SQL DML trigger TT = table Type U = table (user-defined) UQ = Unique Constraint v = view x = Extended stored Procedure
Transferred from: http://www.cnblogs.com/PongorXi/archive/2012/06/20/2556119.html
[Go] Delete all the constraints and forms of MSSQL