Make SQL Server generate SQL for you using SELECT literals

Suppose that I've got an instance of SQL server setup for testing, and I've programmatically created databases on this instance, but never cleaned them up.

This can leave you with hundreds of databases that you'll never use again, and there's no reason for them to continue taking up space and cluttering things which you do not wish to be cluttered.

Now, you could use a GUI tool like enterprise manager, and right click on every database and choose "Delete"

Obviously, that stinks.

Alternatively, you could use isqlw (SQL Query Analyzer) and enter the appropriate T-SQL statement to delete a database:

drop database Foo 

This also stinks, because thats a lot of typing, once you do it for 100 databases

Fortuneately, you can do better. What you've got, fundamentally, is a problem that computers are good at. You've got a lot of somethings (databases) that you want to do something to. You know what you want to do - drop a database. Now you just need to leverage that across a big list of them.

If one wanted to get a list of data in SQL, one would write a a SELECT statement. It turns out that if we're willing to look at the system tables, sql server will tell us about the databases installed in this instance.

select name 
from sysdatabases 
where sid <> 0x01 
and name like 'HW_%' 

Now, this will give you the name of any databases from this instance which match "HW_%" and which have an sid other than 0x01. The sid <> 0x01 clause is interesting. On the SQL instances I'm looking at, the "system" databases always have sid=0x01. But on one other instance, Northwind also has sid=0x01. User databases always have a long gooofy, non-0x01 value for sid. So you can use this as a hack to do a "reasonable" job of excluding "system" databases from your select list. If you can make a determination based on the dbname, as I have above, you can do even better.

Great, we can get SQL server to tell us the list of user databases installed from this instance. What does that buy me? Well, suppose we change the select list:

select 'drop database ', name 
from sysdatabases 
where sid <> 0x01 
and name like 'HW_%' 

If you run this select statement (and you have databases that match the select list) you'll get the following output (at least on my machine - you probably won't get this output as your dbnames will be different

drop database HW_North 
drop database HW_South 
drop database HW_Enterprise 

Congratulations, you've just told SQL server to generate SQL for you. Obviously, if you had 100 databases in your select list, you'd get 100 statements generated. Here's where the hack comes in, i don't know of a way to actually execute this in one step, but I bet it is possible. Instead, I copy and paste the results (results to grid works fine in ISQLW, results as text will give you a giant column width for the name column), back into a command window, and then execute the batch.

This technique is handy for dropping lots of databases, but its also good in other places. For instance, you can generate INSERT or UPDATE statements this way, which may be desirable if you've got just one or two peices that make doing the job with a single insert/update too difficult.

It is not elegant, not efficient, and probably upsets somebody somewhere, but for a quick and dirty management task, using select literals to generate statements for you is a great technique to keep in mind.

Comments (6)

  1. Scott Simons says:

    Look at sp_execresultset. It’s undocumented but it works.

  2. Jared Tullis says:

    Here’s two different ways to go about complete automation, they’re techniques we use almost every day here on our hundreds of databases:


    USE master


    DECLARE @dbname sysname

    DECLARE @SQL nvarchar(1000)


    SELECT name FROM sysdatabases WHERE name like ‘sc_%’ escape ”

    OPEN cur

    FETCH NEXT FROM cur INTO @dbname



    SET @SQL = N’DROP DATABASE ‘ + @dbname

    exec sp_executesql @SQL

    FETCH NEXT FROM cur INTO @dbname


    CLOSE cur


    — END SQL BLOCK 1 —

    Or there’s an undocumented system stored proc in SQL Server 2000 that may or may not be there in future releases:


    exec sp_msforeachdb ‘if CHARINDEX(”SC_”,”?”) = 1 DROP DATABASE ?’

    — END SQL BLOCK 2 —

  3. Joe says:

    It’s generally regarded as good practice to use a system stored procedure to get data from system tables – presumably for compatibility with future versions of SQL Server, so not relevant for a quick and dirty utility like you’re suggesting. But for the record you could do it like this:


    INSERT INTO #TEMP EXECUTE sp_databases

    SELECT ‘drop database ‘ + DATABASE_NAME




    Also are you aware that an underscore in a LIKE expression will match any single character – i.e. ‘HW_%’ will match e.g. ‘HWxxx’ as well as ‘HW_xxx’. To find tables whose name begins with ‘HW_’, you need to escape the underscore: … LIKE ‘HW[_]%’.

  4. Source: Make SQL Server generate SQL for you using SELECT literals. One of the comments to the above article from Joe. CREATE TABLE #TEMP (DATABASE_NAME Sysname, DATABASE_SIZE INT, REMARKS VARCHAR) INSERT INTO #TEMP EXECUTE sp_databases SELECT ‘drop database ‘ DATABASE_NAME

Skip to main content