批量删除指定前缀的表

DECLARE @TmpTb TABLE
    (
      TbName NVARCHAR(100) ,
      Flag INT
    );
 
INSERT  INTO @TmpTb
        SELECT  TABLE_NAME ,
                0 Flag
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_NAME LIKE 'UV_%';
 
DECLARE @Total INT ,
    @RowCount INT ,
    @DropSql NVARCHAR(MAX);
 
SELECT  @Total = COUNT(1)
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME LIKE 'UV_%';

 
SET @RowCount = 1;
SET @DropSql = '';
 
WHILE @RowCount <= @Total
    BEGIN
        DECLARE @TbName VARCHAR(100);
        SELECT TOP 1
                @TbName = TbName
        FROM    @TmpTb
        WHERE   Flag = 0;
        SET @DropSql = @DropSql + 'DROP TABLE ' + @TbName + ';';
        UPDATE  @TmpTb
        SET     Flag = 1
        WHERE   TbName = @TbName;
        SET @RowCount = @RowCount + 1;
    END;
 
EXEC sp_executesql @DropSql;