删除数据库中所有数据和存储过程

实例数据库名称：Cynosure

 --删除数据库中所有数据

 USE Cynosure

DECLARE tablecur CURSOR

FOR

SELECT [name] FROM sysobjects WHERE TYPE='U'

DECLARE @tabname VARCHAR(100)

OPEN tablecur

FETCH NEXT FROM tablecur INTO @tabname

WHILE(@@FETCH_STATUS = 0)

BEGIN  

EXEC('delete from ' + @tabname)

PRINT(@tabname + '表数据已被删除')

 

FETCH NEXT FROM tablecur INTO @tabname

END

CLOSE tablecur

DEALLOCATE tablecur

 

 

--删除数据库中所有存储过程

USE Cynosure

DECLARE proccur CURSOR

FOR

SELECT [name] FROM sysobjects WHERE type='P'

DECLARE @procname varchar(100)

OPEN proccur

FETCH NEXT FROM proccur INTO @procname

WHILE(@@FETCH_STATUS = 0)

BEGIN  

EXEC('drop proc ' + @procname)

PRINT(@procname + '已被删除')

FETCH NEXT FROM proccur INTO @procname

END

CLOSE proccur

DEALLOCATE proccur