SQL删除所有用户自定义数据表,存储过程

有一天,本人在想把数据库重新清理一次,结果郁闷的是没有删除数据库的权限，就更别说新建了。愁。

与是我要写一条SQL删除所有用户自定义数据表,存储过程。

 

执行下面的查询语句，可以得到所有包含在sys.sysobjects视图里的数据

USE AdventureWorks
SELECT *
FROM sys.sysobjects
GO

得出数据后，请注意名为type的列——这一列标明了对象的类型，也就是前面提到的24种。在这里，我用一个表格把它们列出来：

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

OK，我们要得到名称的表（用户自定义表）就是类型为“U”的对象；而sys.objects的类型为“S”。所以，为了达到我们的最终目的，SQL语句应该是——

USE AdventureWorks
SELECT name
FROM sys.sysobjects
WHERE type='U'
GO

 

删除表：

1. --20081008 技术不高，但想出名 NAME：KYE 
3. DECLARE @Tb_Name varchar(30)    --定义游标操作
4. DECLARE staff_cursor CURSOR FOR
5.     SELECT [name] FROM sys.sysobjects
6.     WHERE type='U'  
7. -- 打开游标
8. OPEN staff_cursor 
9. -- 提取记录数据
10. FETCH Next FROM staff_cursor Into @Tb_Name
11.     WHILE @@fetch_status = 0
12.     BEGIN
13.         EXEC('DROP TABLE '  + @Tb_Name )
14.         PRINT @Tb_Name
15.         FETCH Next FROM staff_cursor Into @Tb_Name
16.     END
17. CLOSE staff_cursor       -- 关闭游标
18. DEALLOCATE staff_cursor  -- 释放游标资源

删除存储过程：

 

1. --20081008 技术不高，但想出名 NAME：KYE 
3. DECLARE @Sp_Name varchar(30)    --定义游标操作
4. DECLARE @Tb_Count int
5. SET @Tb_Count = 0
6. DECLARE staff_cursor CURSOR FOR
7.     SELECT [name] FROM sys.sysobjects
8.     WHERE type='p' AND Category =0 --Category =0 表示
9. -- 打开游标
10. OPEN staff_cursor 
11. -- 提取记录数据
12. FETCH Next FROM staff_cursor Into @Sp_Name
13.     PRINT '开始删除存储过程'
14.     WHILE @@fetch_status = 0 
15.     BEGIN
16.         SET @Tb_Count = @Tb_Count + 1
17.         EXEC('DROP PROCEDURE '  + @Sp_Name )
18.         PRINT CONVERT(varchar(20),@Tb_Count) + ': ' + @Sp_Name
19.         FETCH Next FROM staff_cursor Into @Sp_Name
20.     END
21.     print '总共删除' +   CONVERT(varchar(20),@Tb_Count) + '个存储过程'
22. CLOSE staff_cursor       -- 关闭游标
23. DEALLOCATE staff_cursor  -- 释放游标资源

以此类推吧。呵呵。也可删除函数什么的。呵呵。

 

完整版：

 

     declare @procName varchar(500)

      declare cur cursor

            for select [name] from sys.objects where type = 'p'

      open cur 

      fetch next from cur into @procName

      while @@fetch_status = 0

      begin

            if @procName <> 'DeleteAllProcedures'

                  exec('drop procedure ' + @procName)

                  fetch next from cur into @procName

      end

      close cur

      deallocate cur