删除指定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 constraintD = DEFAULT (constraint or stand-alone)F = FOREIGN KEY constraintFN = SQL scalar functionFS = Assembly (CLR) scalar functionFT = Assembly (CLR) table-valued functionIF = SQL inline table-valued functionIT = Internal tableP = SQL stored procedurePC = Assembly (CLR) stored procedurePK = PRIMARY KEY constraintR = Rule (old-style, stand-alone)RF = Replication-filter-procedureS = System base tableSN = SynonymSQ = Service queueTA = Assembly (CLR) DML triggerTF = SQL table-valued-functionTR = SQL DML triggerU = Table (user-defined)UQ = UNIQUE constraintV = ViewX = Extended stored procedure