如何在单发中丢弃表 [英] How do Drop Tables in Single Shot

问题描述

我有批量表开始使用某些名称，如tblEMi％，我想在SQL Server 2005中将其删除一次



请帮助



问候

Nirmala

I have Lot tables Starts with Certain Names like tblEMi% and i want to drop it it one Shot in SQL Server 2005

Pls Help

Regards
Nirmala

推荐答案

阅读： http://blog.sqlauthority.com/2006/11/30/sql-server-cursor-to-process-tables-in-database-with-static-prefix-and- date-created / [ ^ ]



并使用： DROP TABLE [tablename]

使用此..

Use this ..

drop table from sys.tables where name like 'tblEMi%'

如果不完成而不是使用下面的代码。

if Not Done than use below code.

DECLARE @id varchar(200) 
DECLARE @drop varchar(150)
DECLARE @namingPattern varchar(150) 
set @namingPattern = 'tblEMi%'

DECLARE myCursor CURSOR FOR 
    SELECT name FROM sys.tables WHERE name like @namingPattern 

OPEN myCursor 
FETCH next FROM myCursor INTO @id 

WHILE @@fetch_status=0 
BEGIN 
	
    SET @drop = N'drop table ' + @id 
   
    EXECUTE(@drop) 
    
  
    FETCH next FROM myCursor INTO @id 
END 

CLOSE myCursor 
DEALLOCATE myCursor

谢谢

AARIF SHAIKH

Thanks
AARIF SHAIKH

您好Nirmala，在删除所有表之前，您可能需要删除它们之间的所有关系，或者您可能需要以正确的顺序删除这些表。因此，您可以从以下查询中获取所有表的列表。

Hi Nirmala, before dropping all the tables, you might need to delete all the relations between them or perhaps you need to delete those tables in a proper order. So you can get the list of the all the tables from the below query.

SELECT sySchema.name, sysTab.name FROM sys.tables AS sysTab INNER JOIN sys.schemas AS sySchema 
  ON sysTab.[schema_id] = sySchema.[schema_id] WHERE sysTab.name LIKE 'tblEMi%';

所以一旦你对表名感到满意，你就可以运行下面的查询来提供删除表格的脚本。

So once you are happy with the table names, you can run the below query to provide you the scripts to drop the tables.

DECLARE @sqlQry NVARCHAR(MAX) = '';
SELECT @sqlQry += 'DROP TABLE ' + QUOTENAME(sySchema.name) + '.' + QUOTENAME(sysTab.name) + ';'
    FROM sys.tables AS sysTab INNER JOIN sys.schemas AS sySchema ON sysTab.[schema_id] = sySchema.[schema_id]
    WHERE sysTab.name LIKE 'tblEMi%';
SELECT @sqlQry;

这篇关于如何在单发中丢弃表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！