Home > SQL Server > Xóa nhiều bảng trong database SQL Server

Xóa nhiều bảng trong database SQL Server

1. Đối với SQL Server 2005 

DECLARE @id varchar(255) 
DECLARE @dropCommand varchar(255)
DECLARE tableCursor CURSOR FOR 
 SELECT name FROM sys.tables WHERE name <> 'CacheManager'  -- ( điều kiện của bảng muốn xóa )
OPEN tableCursor 
FETCH next FROM tableCursor INTO @id
WHILE @@fetch_status=0 
BEGIN 
 SET @dropcommand = N'drop table ' + @id 
 EXECUTE(@dropcommand) 
 FETCH next FROM tableCursor INTO @id 
END
CLOSE tableCursor 
DEALLOCATE tableCursor

2. Đối với SQL Server 2000

DECLARE @id1 varchar(255),@dropCommand varchar(255)
DECLARE tableCursor CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE='BASE TABLE' and table_name like 'GPN%'
OPEN tableCursor FETCH next FROM tableCursor INTO @id1 WHILE @@fetch_status=0
BEGIN
print 'drop table ' +@id1
FETCH next FROM tableCursor INTO @id1
END
CLOSE tableCursor
DEALLOCATE tableCursor
Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: