Drop all tables whose names begin with a particular string
I would certainly such as a manuscript to go down all tables whose name starts with an offered string. I'm certain this can be performed with some dynamic sql and also the
If any person has a manuscript, or can knock one up promptly, please blog post it.
If no - one blog posts a solution prior to I figure it out myself, I'll upload my remedy.
SELECT 'DROP TABLE "' + TABLE_NAME + '"' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '[prefix]%'
This will certainly create a manuscript.
Including condition to examine presence of table prior to removing:
SELECT 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '[prefix]%'
Thanks Curt, that coincides type of remedy that I was midway via myself.
Yours is better than mine though - it offers itself to very easy alteration. I included a union to the select and also eliminated some consider as well ;)
declare @cmd varchar(4000) declare cmds cursor for Select 'drop table [' + Table_Name + ']' From INFORMATION_SCHEMA.TABLES Where Table_Name like 'prefix%' union Select 'drop view [' + Table_Name + ']' From INFORMATION_SCHEMA.VIEWS Where Table_Name like 'prefix%' open cmds while 1=1 begin fetch cmds into @cmd if @@fetch_status != 0 break exec(@cmd) end close local deallocate local
Don't stress, it is not a manufacturing data source - this is simply for very easy tidy - up of my dev db while I attempt things out.
Xenph Yan is solution was much cleaner than mine yet below is mine just the same.
DECLARE @startStr AS Varchar (20) SET @startStr = 'tableName' DECLARE @startStrLen AS int SELECT @startStrLen = LEN(@startStr) SELECT 'DROP TABLE ' + name FROM sysobjects WHERE type = 'U' AND LEFT(name, @startStrLen) = @startStr
tableName to the personalities that you intend to look with.
You might require to change the question to include the proprietor if there is greater than one in the data source.
DECLARE @cmd varchar(4000) DECLARE cmds CURSOR FOR SELECT 'drop table [' + Table_Name + ']' FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE 'prefix%' OPEN cmds WHILE 1 = 1 BEGIN FETCH cmds INTO @cmd IF @@fetch_status != 0 BREAK EXEC(@cmd) END CLOSE cmds; DEALLOCATE cmds
This is cleaner than making use of a 2 - action strategy of create manuscript plus run. Yet one benefit of the manuscript generation is that it offers you the opportunity to assess the totality of what is mosting likely to be run prior to it is in fact run.
I recognize that if I were mosting likely to do this versus a manufacturing data source, I would certainly be as mindful as feasible.
Edit Code example dealt with.