Code Generation, T-SQL Style
Making your T-SQL work smarter for you by having it work as a code generator. Part 1 in a series.
- By Joshua Jones
- 04/05/2010
Want to save some time writing code? Many DBAs use the trick I'm going to show you this week to: A) build scripts faster, and B) run ad-hoc queries faster. Essentially, you use SELECT statements as well as the T-SQL functions PRINT and EXEC() to build and display/execute T-SQL commands.
Check out this script:
SELECT DISTINCT – Make sure we don't get duplicates
'EXEC ' + db_name()
+ '.dbo.sp_change_users_login ''Update_One'', '
+ '''' + U.name + ''', '
+ '''' + U.name + '''' AS DeOrphanizeStatement
FROM sys.database_principals AS U
LEFT OUTER JOIN sys.sql_logins AS L
ON L.[name] = U.[name] COLLATE SQL_Latin1_General_CP1_CI_AS --Ensures no Case problems
where (U.type = 'S') --SQL Logins Only; no Windows Logins, no Roles
AND U.name NOT IN ('dbo')
AND U.sid IS NOT NULL --Eliminates INFORMATION_SCHEMA and PUBLIC
AND U.name NOT IN ('guest') --Eliminates specific unwanted logins
Walking through the script, we can see that we're simply using a SELECT with a combination of explicit string values concatenated with values we are retrieving from various system tables. In this case, we're building commands that will help us execute the system stored procedure sp_change_users_login to restore the SIDs of SQL logins in a database. You might find you'll need to do this when restoring a copy of a database from one server to another. Fig. 1 shows sample output from this script.
 |
| Figure 1. Results from running the script to snag orphaned users. |
We could now use this script in multiple databases, or even use the system stored procedure sp_msForEachdb to iterate through all of our databases and build a script to execute these commands as needed.
Now, let's say we want to take this a step further, and have the script actually generate AND execute the resultant T-SQL WITHOUT sp_msForEachdb. In this case, sp_change_users_login can run against logins that are OK without doing any damage, so we could run through all of our databases and make sure there are no orphaned users.
Next time, I'll cover using variables and the PRINT and EXEC() functions to do this even faster. Until then, have fun!
About the Author
Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.