批量删除数据库中有特定开始字符的表、视图和存储过程

USE 数据库名

GO

DECLARE @SQL VARCHAR(8000)

DECLARE @TYPE VARCHAR(2)

DECLARE @PREVSTR VARCHAR(20)

DECLARE @TEMPSQL VARCHAR(150)

SET @TYPE='U'

SET @PREVSTR='FP_OLDPROD_%'

SET @TEMPSQL='DROP TABLE '

WHILE (SELECT COUNT(*) FROM SYSOBJECTS WHERE TYPE=@TYPE AND NAME LIKE @PREVSTR )>0

BEGIN

SELECT @SQL=@TEMPSQL + NAME FROM SYSOBJECTS WHERE (TYPE = @TYPE) AND NAME LIKE @PREVSTR ORDER BY @TEMPSQL + NAME

PRINT @SQL

EXEC(@SQL)

END

SET @TYPE='V'

SET @PREVSTR='FP_OLDPROD_%'

SET @TEMPSQL='DROP VIEW '

SET @SQL=''

WHILE (SELECT COUNT(*) FROM SYSOBJECTS WHERE TYPE=@TYPE AND NAME LIKE @PREVSTR )>0

BEGIN

SELECT @SQL=@TEMPSQL + NAME FROM SYSOBJECTS WHERE (TYPE = @TYPE) AND NAME LIKE @PREVSTR ORDER BY @TEMPSQL + NAME

PRINT @SQL

EXEC(@SQL)

END

SET @TYPE='P'

SET @PREVSTR='FP_OLDPROD_%'

SET @TEMPSQL='DROP PROC '

SET @SQL=''

WHILE (SELECT COUNT(*) FROM SYSOBJECTS WHERE TYPE=@TYPE AND NAME LIKE @PREVSTR )>0 BEGIN SELECT @SQL=@TEMPSQL + NAME FROM SYSOBJECTS WHERE (TYPE = @TYPE) AND NAME LIKE @PREVSTR ORDER BY @TEMPSQL + NAME

PRINT @SQL

EXEC(@SQL)

END