2005191540@SQL模糊查表并循环删除 | LOFT.CLUEE.TECH

USE  [KGKXDB-14-JIANKE];

SELECT ROW_NUMBER() OVER (ORDER BY [sysobjects].[id] ASC) AS rowid,
       [sysobjects].[name]
INTO #Temp1
FROM sysobjects
WHERE name LIKE 'GpsCar_%';

DECLARE @minRowId INT;
DECLARE @maxRowId INT;

SELECT @minRowId = MIN([T].[rowid]),
       @maxRowId = MAX([T].[rowid])
FROM [#Temp1] AS [T];

WHILE @minRowId <= @maxRowId
BEGIN

    DECLARE @delTab NVARCHAR(512);

    SELECT @delTab = [T].[name]
    FROM [#Temp1] AS [T]
    WHERE [T].[rowid] = @minRowId;
    DECLARE @sql NVARCHAR(512) = N'DROP TABLE [' + @delTab+']';

    PRINT @sql;

     EXEC sp_executesql @sql;
    SET @minRowId = @minRowId + 1;
END;

--DROP TABLE [#Temp1]

--删除表
--DECLARE @delete_sql NVARCHAR(MAX) = ''
--SELECT @delete_sql += ('
--DROP TABLE '+name+';') FROM sysobjects WHERE xtype='u' AND name LIKE 'GpsCar_%'
----PRINT @delete_sql
--EXEC (@delete_sql)