Published on: 04 Jan, 2014

How to delete all tables with specific prefix in one go using while loop in SQL Server?

Tumblr Digg Google Plus

You may wonder how you can delete all tables with specific prefix in a table in one go in SQL Server. It will save your time especially if you have hundred of tables rather than delete one by one, you can use the following script to do the easy job for you.

/*  Run this command in sql query panel, you just need to replace 'Prefix_TableName' to your prefix table' */
declare @query varchar(4000)
declare icursor cursor for
	Select 'drop table [' + Table_Name + ']'
	From    INFORMATION_SCHEMA.TABLES
	Where   Table_Name like 'Prefix_TableName%'
	union
	Select 'drop view [' + Table_Name + ']'
	From    INFORMATION_SCHEMA.VIEWS
	Where   Table_Name like 'Prefix_TableName%'
	open icursor
	while 1=1
	begin
		fetch icursor into @query
		if @@fetch_status != 0 break
		exec(@query)
	end
close icursor
deallocate icursor
GO

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.