SQL query to delete databases with specific string

Question

I know this has been asked in some contexts but I cannot follow how they are working. If there are plenty of useful answers to this question then just down vote me but please link the relevant questions before you do.

I am trying to write a query that will run in a batch file to automatically delete tables that have a specific string in their name. For example, when I run the batch file, I want it to look for tables that might have a ABCDir in the file. I have tried some various ways to get this to work with no success yet.

I know this is close to what I'm trying to do. Or I guess it could be way off lol.

DECLARE db_cursor CURSOR FOR
DELETE name FROM sys.database
WHERE name LIKE '%ABCDir%'

Do you want to delete the contents of the table or drop the table? The example posted by Raj Moore will drop the tables. — Sean Lange
– Sean Lange, Commented Nov 17, 2014 at 21:03

Raj More · Accepted Answer · 2014-11-17 20:23:36Z

2

Warning This is a very dangerous practice. Consider renaming the tables instead of delete.

Answer

You will have to create Dynamic SQL as an example of how to drop tables (I just couldn't bring myself to write DROP DATABASE)

Declare @strSQL nVarChar (Max) = ';
'

select @strSQL = @strSQL  + 'Drop Table ' + T.TABLE_SCHEMA + '.'+ TABLE_NAME + ';
'
From INFORMATION_SCHEMA.TABLES   T
Where Table_Name Like '%ABC1234%'

Print @strSQL
Exec Sp_ExecuteSQL @strSQL

answered Nov 17, 2014 at 20:23

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JohnN Over a year ago

I hope you'll forgive my ignorance, I'm new to SQL. Does this query work for any database or do I need to put relevant information in the TABLE_SCHEMA and TABLE_NAME spots?

Raj More Over a year ago

This would work for any databases that you have permissions to.

TT. Over a year ago

Best use QUOTENAME for the schema and table name, you never know if there's any names with spaces or other special characters in them.

Collectives™ on Stack Overflow

SQL query to delete databases with specific string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related