sql server - Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

ID : 10198

viewed : 44

Tags : sqlsql-serversql-server-2005tsqlsql

Top 5 Answer for sql server - Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

vote vote

93

this script cleans all views, SPS, functions PKs, FKs and tables.

/* 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 
vote vote

86

I'm using this script by Adam Anderson, updated to support objects in other schemas than 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 

Source: an Adam Anderson blog post

vote vote

77

The best thing to do it is "Generate scripts for Drop"

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts

after choosing objects in set Scripting option click Advanced Button

  • -> 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

This way we can customize our script.

vote vote

60

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.

vote vote

51

I'd do it in two statements: DROP DATABASE ???

and then CREATE DATABASE ???

Top 3 video Explaining sql server - Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Related QUESTION?