--Delete all constraints, tables, views, and other SQL scripts--###############################################--Delete all foreign KEY constraints--############################## ################ #DECLARE @SQL VARCHAR DECLARE cur_constraint CURSOR LOCAL forselect ' ALTER TABLE ' +case when O.schema_ 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_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_constraintfetch Cur_constraint to @SQLWHILE @ @FETCH_STATUS =0beginexec (@SQL) FETCH cur_constraint into @SQLENDCLOSE cur_ Constraintdeallocate cur_constraint--###############################################--Delete all views (stored procedures, functions, etc. in the same way)--# ############################################# #DECLARE @SQL VARCHAR (DECLARE) cur_view CURSOR LOCAL forselect ' IF OBJECT_ID ("+case when o.schema_id is 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 was not null and 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 A s s on O.schema_id=s.schema_idwhere o.type inch (' V ') OPEN cur_viewfetch cur_view into @SQLWHILE @ @FETCH_STATUS =0beginexec (@ SQL) FETCH Cur_view into @SQLENDCLOSE 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.schema_ Id=s.schema_idwhere o.type= ' U ' OPEN cur_tablefetch cur_table into @SQLWHILE @ @FETCH_STATUS =0beginexec (@SQL) FETCH cur_ TABLE into @SQLENDCLOSE cur_tabledeallocate cur_table--############################################## #再提供一下SQL OB in the serverject_id function object_type parameter type--############################################## #OBJECT_ID (object_name,object_type) Object type: AF = aggregate function (CLR) C = Check Constraint d = DEFAULT (constraint or stand-alone) 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 Filter 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
Delete all constraints, tables, views, and other SQL scripts