-----------------------------刪除數(shù)據(jù)表---------------------
DECLARE @au_lname VARCHAR(40),@SQLString NVARCHAR(500)
DECLARE tb CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='U' and name like 'dnt%'
OPEN tb
-- Perform the FIRST FETCH.
FETCH NEXT FROM tb INTO @au_lname
-- CHECK @@FETCH_STATUS TO see IF there ARE ANY more ROWS TO FETCH.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This IS executed AS long AS the previous FETCH succeeds.
FETCH NEXT FROM tb INTO @au_lname
SET @SQLString='DROP TABLE '+ @au_lname
EXEC sp_executesql @SQLString
--DROP TABLE @au_lname
END
CLOSE tb
DEALLOCATE tb
GO
-----------------------------刪除存儲(chǔ)過(guò)程---------------------
DECLARE @au_lname VARCHAR(40),@SQLString NVARCHAR(500)
DECLARE tb CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='p' and name like 'dnt%'
OPEN tb
-- Perform the FIRST FETCH.
FETCH NEXT FROM tb INTO @au_lname
-- CHECK @@FETCH_STATUS TO see IF there ARE ANY more ROWS TO FETCH.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This IS executed AS long AS the previous FETCH succeeds.
FETCH NEXT FROM tb INTO @au_lname
SET @SQLString='DROP PROCEDURE '+ @au_lname
EXEC sp_executesql @SQLString
--DROP TABLE @au_lname
END
CLOSE tb
DEALLOCATE tb
GO