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 INFORMATION_SCHEMA tables.

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.

0
2019-05-13 03:43:16
Source Share
Answers: 4
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]%'
0
2019-05-17 14:31:56
Source

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.

0
2019-05-17 06:09:13
Source

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

Just transform tableName to the personalities that you intend to look with.

0
2019-05-17 05:24:51
Source

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.

0
2019-05-17 05:22:55
Source