Friday, July 1, 2011

Generate scripts for multiple databases for the same table

Problem:
How do you delete the same table and stored procs from a bunch of different databases all in one instance? For example, we have 100 companies in an organization, and we install our product on a per company basis. If we have 20 tables for our product and it's installed on every database, we have 2000 tables to delete. There's no way I'm doing this by hand! *Note: We can't just delete the database, because our product is only a module within a larger system containing multiple products.I did this with some vicious looking SQL. For each company database it executes dynamic SQL creating a cursor on dynamic SQL statements for dropping tables. The reason i'm dynamically creating cursors is because they have to be in the context of the company database. This has to be dynamic SQL otherwise i'd have to know the name of the database beforehand, and remember how I have 100 companies? Yeah, definitely not going to do 100 different use statements by hand.

Solution:


USE <SharedSystemDatabase>
DECLARE @dbName varchar(500), @sql varchar(500), @dropTbl varchar(500)
--Get all company databases + system database
DECLARE DBs CURSOR FOR
SELECT INTERID FROM SY01500 UNION SELECT '<SharedSystemDatabase>'
OPEN DBs

WHILE 1=1
BEGIN
FETCH NEXT FROM DBs INTO @dbName
IF @@FETCH_STATUS != 0
BREAK
--Generate drop statements for all this DB's specified tables
SET @sql =
'USE ' + @dbName +
' DECLARE tbls CURSOR FOR SELECT ''USE ' + @dbName + ' DROP TABLE ['' + TABLE_NAME + '']'' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''<Our Product Tables System-unique Prefix>%'''
EXEC(@sql)
OPEN tbls

WHILE 1=1
BEGIN
FETCH NEXT FROM tbls INTO @dropTbl
IF @@FETCH_STATUS != 0
BREAK
print(@dropTbl) -- Change this exec(@dropTbl)
END
CLOSE tbls
DEALLOCATE tbls


--Notice, the loop is unrolled. This is because it would've been a pain to try to concat @sql even --more than it already is
SET @sql =
'USE ' + @dbName +
' DECLARE tbls CURSOR FOR SELECT ''USE ' + @dbName + ' DROP PROCEDURE ['' + SPECIFIC_NAME + '']'' FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME LIKE ''zDP_
Our Product Tables System-unique Prefix%'''
EXEC(@sql)
OPEN tbls

WHILE 1=1
BEGIN
FETCH NEXT FROM tbls INTO @dropTbl
IF @@FETCH_STATUS != 0
BREAK
print(@dropTbl) -- Change this exec(@dropTbl)
END
CLOSE tbls
DEALLOCATE tbls

END
CLOSE DBs
DEALLOCATE DBs

No comments:

Post a Comment

There was an error in this gadget