132
刪除指定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字段含義：
補充：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 |
最後更新：2017-04-03 20:19:55