在一个 sql 语句中删除所有表、存储过程、触发器、约束和所有依赖项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/536350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
提问by renegadeMind
Is there any way in which I can clean a database in SQl Server 2005 by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one SQL statement?
有没有什么方法可以通过删除所有表并删除一个 SQL 语句中的存储过程、触发器、约束和所有依赖项来清理 SQl Server 2005 中的数据库？
REASON FOR REQUEST:
请求的理由：
I want to have a DB script for cleaning up an existing DB which is not in use rather than creating new ones, especially when you have to put in a request to your DB admin and wait for a while to get it done!
我想要一个数据库脚本来清理未使用的现有数据库而不是创建新数据库，尤其是当您必须向数据库管理员提出请求并等待一段时间才能完成时！
回答by
this script cleans all views, SPS, functions PKs, FKs and tables.
此脚本清除所有视图、SPS、函数 PK、FK 和表。
/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
EXEC (@SQL)
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
回答by Vlad Iliescu
I'm using this script by Adam Anderson, updated to support objects in other schemas than dbo.
我正在使用 Adam Anderson 的这个脚本，更新后支持除 dbo 之外的其他模式中的对象。
declare @n char(1)
set @n = char(10)
declare @stmt nvarchar(max)
-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
from sys.procedures
-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.check_constraints
-- functions
select @stmt = isnull( @stmt + @n, '' ) +
'drop function [' + schema_name(schema_id) + '].[' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )
-- views
select @stmt = isnull( @stmt + @n, '' ) +
'drop view [' + schema_name(schema_id) + '].[' + name + ']'
from sys.views
-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys
-- tables
select @stmt = isnull( @stmt + @n, '' ) +
'drop table [' + schema_name(schema_id) + '].[' + name + ']'
from sys.tables
-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
'drop type [' + schema_name(schema_id) + '].[' + name + ']'
from sys.types
where is_user_defined = 1
exec sp_executesql @stmt
回答by JP Emvia
The best thing to do it is "Generate scripts for Drop"
最好的办法是“为 Drop 生成脚本”
Select Database-> Right Click-> Tasks-> Generate Scripts- will open wizardfor generating scripts
选择数据库- >右键点击- >任务- >生成脚本-将打开向导生成的脚本
after choosing objects in set Scripting option click AdvancedButton
在设置脚本选项中选择对象后，单击高级按钮
- -> Set option 'Script to create' to true (want to create)
- -> Set option 'Script to Drop' to true (want to drop)
- -> Select the Check box to select objects wish to create script
- -> Select the choice to write script (File, New window, Clipboard)
It includes dependent objects by default.(and will drop constraint at first)
Execute the script
- -> 将选项“创建脚本”设置为 true（想要创建）
- -> 将选项“删除脚本”设置为 true（想要删除）
- -> 选中复选框以选择希望创建脚本的对象
- -> 选择编写脚本的选项（文件、新窗口、剪贴板）
它默认包含依赖对象。（并且首先会删除约束）
执行脚本
This way we can customize our script.
这样我们就可以自定义我们的脚本。
回答by Quassnoi
To drop all tables:
删除所有表：
exec sp_MSforeachtable 'DROP TABLE ?'
This will, of course, drop all constraints, triggers etc., everything but the stored procedures.
当然，这将删除所有约束、触发器等，除了存储过程之外的所有内容。
For the stored procedures I'm afraid you will need another stored procedure stored in master.
对于存储过程，恐怕您将需要另一个存储在master.
回答by Xn0vv3r
I'd do it in two statements: DROP DATABASE ???
我会用两个语句来做： DROP DATABASE ???
and then CREATE DATABASE ???
回答by David Roussel
I tried some of the script here, but they didn't work for me, as I have my tables in schemas. So I put together the following. Note that this script takes a list of schemas, and drops then in sequence. You need to make sure that you have a complete ordering in your schemas. If there are any circular dependencies, then it will fail.
我在这里尝试了一些脚本，但它们对我不起作用，因为我的表中有模式。所以我整理了以下内容。请注意，此脚本采用模式列表，然后按顺序删除。您需要确保您的架构中有完整的排序。如果有任何循环依赖，那么它将失败。
PRINT 'Dropping whole database'
GO
------------------------------------------
-- Drop constraints
------------------------------------------
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE ['+tc2.CONSTRAINT_SCHEMA+'].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
Exec (@Sql)
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
------------------------------------------
-- Drop views
------------------------------------------
DECLARE @sql VARCHAR(MAX) = ''
, @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;
SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM sys.views v
PRINT @sql;
EXEC(@sql);
GO
------------------------------------------
-- Drop procs
------------------------------------------
PRINT 'Dropping all procs ...'
GO
DECLARE @sql VARCHAR(MAX) = ''
, @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;
SELECT @sql = @sql + 'DROP PROC ' + QUOTENAME(SCHEMA_NAME(p.schema_id)) + '.' + QUOTENAME(p.name) +';' + @crlf
FROM [sys].[procedures] p
PRINT @sql;
EXEC(@sql);
GO
------------------------------------------
-- Drop tables
------------------------------------------
PRINT 'Dropping all tables ...'
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO
------------------------------------------
-- Drop sequences
------------------------------------------
PRINT 'Dropping all sequences ...'
GO
DECLARE @DropSeqSql varchar(1024)
DECLARE DropSeqCursor CURSOR FOR
SELECT DISTINCT 'DROP SEQUENCE ' + s.SEQUENCE_SCHEMA + '.' + s.SEQUENCE_NAME
FROM INFORMATION_SCHEMA.SEQUENCES s
OPEN DropSeqCursor
FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
PRINT @DropSeqSql
EXECUTE( @DropSeqSql )
FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql
END
CLOSE DropSeqCursor
DEALLOCATE DropSeqCursor
GO
------------------------------------------
-- Drop Schemas
------------------------------------------
DECLARE @schemas as varchar(1000) = 'StaticData,Ird,DataImport,Collateral,Report,Cds,CommonTrade,MarketData,TypeCode'
DECLARE @schemasXml as xml = cast(('<schema>'+replace(@schemas,',' ,'</schema><schema>')+'</schema>') as xml)
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT sql = 'DROP SCHEMA ['+schemaName+']' FROM
(SELECT CAST(T.schemaName.query('text()') as VARCHAR(200)) as schemaName FROM @schemasXml.nodes('/schema') T(schemaName)) as X
JOIN information_schema.schemata S on S.schema_name = X.schemaName
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
Exec (@Sql)
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
回答by DareDevil
This is what I have tried:
这是我尝试过的：
SELECT 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables
What ever the output it will print, just copy all and paste in new query and press execute. This will delete all tables.
无论它将打印什么输出，只需复制所有内容并粘贴到新查询中，然后按执行即可。这将删除所有表。
回答by A-K
Back up a completely empty database. Instead of dropping all the objects, just restore the backup.
备份一个完全空的数据库。不要删除所有对象，只需恢复备份即可。
回答by judoka
I accidentally ran a db init script against my master database tonight. Anyways, I quickly ran into this thread. I used the: exec sp_MSforeachtable 'DROP TABLE ?' answer, but had to execute it multiple times until it didn't error (dependencies.) After that I stumbled upon some other threads and pieced this together to drop all the stored procedures and functions.
今晚我不小心对我的主数据库运行了一个 db init 脚本。无论如何，我很快就遇到了这个线程。我使用了：exec sp_MSforeachtable 'DROP TABLE ?' 答案，但必须多次执行它直到它没有错误（依赖关系）。之后我偶然发现了一些其他线程并将其拼凑在一起以删除所有存储过程和函数。
DECLARE mycur CURSOR FOR select O.type_desc,schema_id,O.name
from
sys.objects O LEFT OUTER JOIN
sys.extended_properties E ON O.object_id = E.major_id
WHERE
O.name IS NOT NULL
AND ISNULL(O.is_ms_shipped, 0) = 0
AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
AND ( O.type_desc = 'SQL_STORED_PROCEDURE' OR O.type_desc = 'SQL_SCALAR_FUNCTION' )
ORDER BY O.type_desc,O.name;
OPEN mycur;
DECLARE @schema_id int;
DECLARE @fname varchar(256);
DECLARE @sname varchar(256);
DECLARE @ftype varchar(256);
FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sname = SCHEMA_NAME( @schema_id );
IF @ftype = 'SQL_STORED_PROCEDURE'
EXEC( 'DROP PROCEDURE "' + @sname + '"."' + @fname + '"' );
IF @ftype = 'SQL_SCALAR_FUNCTION'
EXEC( 'DROP FUNCTION "' + @sname + '"."' + @fname + '"' );
FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;
END
CLOSE mycur
DEALLOCATE mycur
GO
回答by Sakthivel Murugesan
Try this
尝试这个
Select 'ALTER TABLE ' + Table_Name +' drop constraint ' + Constraint_Name from Information_Schema.CONSTRAINT_TABLE_USAGE
Select 'drop Procedure ' + specific_name from Information_Schema.Routines where specific_name not like 'sp%' AND specific_name not like 'fn_%'
Select 'drop View ' + table_name from Information_Schema.tables where Table_Type = 'VIEW'
SELECT 'DROP TRIGGER ' + name FROM sysobjects WHERE type = 'tr'
Select 'drop table ' + table_name from Information_Schema.tables where Table_Type = 'BASE TABLE'
