Share via

Make SQL Server generate SQL for you using SELECT literals

  • Article

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 

 go

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

  • Anonymous
    November 23, 2004
    Look at sp_execresultset. It's undocumented but it works.
  • Anonymous
    November 23, 2004
    Here's two different ways to go about complete automation, they're techniques we use almost every day here on our hundreds of databases:

    -- BEGIN SQL BLOCK 1 --
    USE master
    GO
    DECLARE @dbname sysname
    DECLARE @SQL nvarchar(1000)

    DECLARE cur CURSOR FORWARD_ONLY KEYSET FOR

    SELECT name FROM sysdatabases WHERE name like 'sc_%' escape ''

    OPEN cur
    FETCH NEXT FROM cur INTO @dbname
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = N'DROP DATABASE ' + @dbname
    exec sp_executesql @SQL
    FETCH NEXT FROM cur INTO @dbname
    END
    CLOSE cur
    DEALLOCATE 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:

    -- BEGIN SQL BLOCK 2 --
    exec sp_msforeachdb 'if CHARINDEX(''SC_'',''?'') = 1 DROP DATABASE ?'
    -- END SQL BLOCK 2 --
  • Anonymous
    November 23, 2004
    The comment has been removed
  • Anonymous
    November 24, 2004
    The comment has been removed