主页 / dba / 问题 / 5526
Accepted
Cell-o
Asked: 2011-09-09 06:18:49 +0800 CST

如何在一个查询中删除多个具有共同前缀的表？

  • 772

我正在使用 Microsoft SQL Server 2008。我的问题是：如何在一个查询中删除多个具有共同前缀的表？

类似于表名的东西：

LG_001_01_STLINE, 
LG_001_02_STFICHE
sql-server sql-server-2008

6 个回答

  1. Best Answer

    您可以使用目录视图构建字符串，例如：

    DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'LG_001%';

PRINT @sql;
-- EXEC sp_executesql @sql;

    当然也有潜在的陷阱，例如，如果这些表有外键关系，您要么需要先删除它们，要么安排输出以按特定顺序删除表。

    要获取表列表，请使用：

    SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';
    • 36

  2. 我运行了这个查询，然后将结果粘贴回查询窗口以删除所有表：

    SELECT 'DROP TABLE ' + NAME from sys.tables
ORDER BY NAME

    如果要删除所有表但保留名称以 A、B、C 或 D 开头的表：

    SELECT 'DROP TABLE ' + NAME from sys.tables
WHERE NAME NOT LIKE '[ABCD]%'
GROUP BY NAME
    • 6

  3. 这允许您删除更多数量的表。

    declare 
@cursor as cursor, 
@FTABLE as varchar(500) 
set @cursor = CURSOR FOR 
select 'drop table ' + NAME + ';' 
from sys.tables 
where not SUBSTRING(NAME,10,3) in 
( 
'310', 
'311', 
'312', 
'313', 
'314', 
'320', 
'321', 
'322' 
) 
open @cursor 
fetch next from @cursor into @FTABLE 
while (@@FETCH_STATUS =0) 
begin 
        exec(@FTABLE) 
        print @FTABLE 
fetch next from @cursor into @FTABLE 
end 
close @cursor 
deallocate @cursor
    • -1

  4. 我喜欢我写的这个：

      DECLARE @chv_LG001_TableName nvarchar (100)
  DECLARE @chv_DROP_LG001_Tables nvarchar(100)
  DECLARE @chv_LG001_Table_Count int

  SET @chv_LG001_Table_Count = (SELECT count(OBJECT_NAME(id))
        FROM SYSINDEXES
        WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2
            and OBJECT_NAME(id) like 'LG_001%')

 IF @chv_LG001_Table_Count > 0
    BEGIN

    DECLARE  Drop_LG001_Tables_Cursor CURSOR FOR
      -- This query will give you the table list you are wanting
        SELECT OBJECT_NAME(id)
        FROM SYSINDEXES
        WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2
            and OBJECT_NAME(id) like 'LG_001%'

    OPEN Drop_LG001_Tables_Cursor
    FETCH NEXT FROM Drop_LG001_Tables_Cursor INTO @chv_LG001_TableName 
    WHILE @@FETCH_STATUS = 0 

    BEGIN           

    SET @chv_DROP_LG001_Tables = 'DROP TABLE ' + '[' + @chv_LG001_TableName + ']'

    --Print @chv_DROP_LG001_Tables 
-- Uncomment the next line when you are ready because it WILL clear out these tables. 
    --EXEC sp_executesql @chv_DROP_LG001_Tables

    FETCH NEXT FROM Drop_LG001_Tables_Cursor
    INTO @chv_LG001_TableName

    END

    CLOSE Drop_LG001_Tables_Cursor
    DEALLOCATE Drop_LG001_Tables_Cursor

END
    • -1

  5. 这可以使用execute如下方式完成：

    declare @sql1 nvarchar(max) 
SELECT @sql1 =
 STUFF(
  (
    select ' drop table dbo.[' + name + ']'

FROM         sys.sysobjects AS sobjects
WHERE     (xtype = 'U') AND (name LIKE 'GROUP_BASE_NEW_WORK_%')
        for xml path('')
   ),
        1,1,'')

        execute sp_executesql @sql1
    • -2 
  6. SELECT s.name, t.name 
  FROM sys.tables AS t 
  INNER JOIN sys.schemas AS s 
  ON t.[schema_id] = s.[schema_id] 
  WHERE t.name LIKE 'LG_001%';

    运行上述查询并将结果保存到 csv 中。然后在记事本中打开该 CSV。然后按 Ctrl+H 用 DROP TABLE SCHEMA 替换模式，这将为您提供所有删除查询，将这个大 sql 复制并粘贴到您的 sql 工具中并执行

    如果你的结果是

    myschema.table1
myschema.table2

    替换后是这个样子

    DROP TABLE MYSCHEMA.TABLE1
DROP TABLE MYSCHEMA.TABLE2
    • -3

相关问题