删除指定SQL数据库所有表和存储过程

原理就是根据 sysobjects 系统表里面查到每张表名，然后drop掉，同理可以一次性drop所有存储过程

--删所有数据表

USE [数据库名]

DECLARE @tableName VARCHAR(Max)

SET @tableName = ''

SELECT @tableName = @tableName + NAME + ','

FROM   sysobjects

WHERE  xtype = 'U'; 

SELECT @tableName = 'drop table ' + LEFT(@tableName, (LEN(@tableName) -1))

EXEC (@tableName)

 

--删所有存储过程

USE [数据库名]

DECLARE @procedureName VARCHAR(Max)

SET @procedureName = ''

SELECT @procedureName = @procedureName + NAME + ','

FROM   sysobjects

WHERE  xtype = 'P';   

SELECT @procedureName = 'DROP PROCEDURE ' + LEFT(@procedureName, (LEN(@procedureName) -1))

EXEC (@procedureName)

补充：sysobjects表里type字段含义：

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = 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

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure