原理就是根据 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字段含义：
补充：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 |