批量删除表
DECLARE @StartStr NVARCHAR(100)='T_Info_'
SELECT TABLE_NAME,0 Flag FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @StartStr+'_%'
DECLARE @TmpTb TABLE(
TbName NVARCHAR(100),
Flag INT
)
INSERT INTO @TmpTb
SELECT TABLE_NAME,0 Flag FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @StartStr+'_%'
DECLARE @Total INT,
@RowCount INT,
@DropSql NVARCHAR(MAX);
SELECT @Total=COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @StartStr+'_%'
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
PRINT @DropSql;
批量删除表数据
DECLARE @StartStr NVARCHAR(100)='T_Info_'
SELECT TABLE_NAME,0 Flag FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @StartStr+'%'
DECLARE @TmpTb TABLE(
TbName NVARCHAR(100),
Flag INT
)
INSERT INTO @TmpTb
SELECT TABLE_NAME,0 Flag FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @StartStr+'%'
DECLARE @Total INT,
@RowCount INT,
@DropSql NVARCHAR(MAX);
SELECT @Total=COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @StartStr+'%'
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+'TRUNCATE TABLE '+@TbName+';'
UPDATE @TmpTb SET Flag=1 WHERE TbName=@TbName
SET @RowCount = @RowCount + 1
END
EXEC sp_executesql @DropSql
PRINT @DropSql;