Drop tables/constraints/view/function/store procedure

sometimes, we have no rights to drop the whole database in SQL SERVER. But we still want to clear the the database and recreate tables/constraints/views/functions/store procedure etc.

The following is the script. use at your risk.

  1 /* Drop all non-system stored procs */
  2 DECLARE @name VARCHAR(128)
  3 DECLARE @SQL VARCHAR(254)
  4 
  5 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
  6 
  7 WHILE @name is not null
  8 BEGIN
  9     SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
 10     EXEC (@SQL)
 11     PRINT 'Dropped Procedure: ' + @name
 12     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
 13 END
 14 GO
 15 
 16 /* Drop all views */
 17 DECLARE @name VARCHAR(128)
 18 DECLARE @SQL VARCHAR(254)
 19 
 20 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
 21 
 22 WHILE @name IS NOT NULL
 23 BEGIN
 24     SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
 25     EXEC (@SQL)
 26     PRINT 'Dropped View: ' + @name
 27     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
 28 END
 29 GO
 30 
 31 /* Drop all functions */
 32 DECLARE @name VARCHAR(128)
 33 DECLARE @SQL VARCHAR(254)
 34 
 35 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])
 36 
 37 WHILE @name IS NOT NULL
 38 BEGIN
 39     SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
 40     EXEC (@SQL)
 41     PRINT 'Dropped Function: ' + @name
 42     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])
 43 END
 44 GO
 45 
 46 /* Drop all Foreign Key constraints */
 47 DECLARE @name VARCHAR(128)
 48 DECLARE @constraint VARCHAR(254)
 49 DECLARE @SQL VARCHAR(254)
 50 
 51 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)
 52 
 53 WHILE @name is not null
 54 BEGIN
 55     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)
 56     WHILE @constraint IS NOT NULL
 57     BEGIN
 58         SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
 59         EXEC (@SQL)
 60         PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
 61         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)
 62     END
 63 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)
 64 END
 65 GO
 66 
 67 /* Drop all Primary Key constraints */
 68 DECLARE @name VARCHAR(128)
 69 DECLARE @constraint VARCHAR(254)
 70 DECLARE @SQL VARCHAR(254)
 71 
 72 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)
 73 
 74 WHILE @name IS NOT NULL
 75 BEGIN
 76     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)
 77     WHILE @constraint is not null
 78     BEGIN
 79         SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
 80         EXEC (@SQL)
 81         PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
 82         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)
 83     END
 84 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)
 85 END
 86 GO
 87 
 88 /* Drop all tables */
 89 DECLARE @name VARCHAR(128)
 90 DECLARE @SQL VARCHAR(254)
 91 
 92 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
 93 
 94 WHILE @name IS NOT NULL
 95 BEGIN
 96     SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
 97     EXEC (@SQL)
 98     PRINT 'Dropped Table: ' + @name
 99     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
100 END
101 GO
View Code

 

posted @ 2013-11-08 17:32  Cathy Lee  阅读(254)  评论(0)  编辑  收藏  举报