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.

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'', '
    + '''' + + ''', '
    + '''' + + '''' 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 NOT IN ('dbo')
    AND 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.


  • What Money in Excel Means for the Future of Microsoft 365 Apps

    Microsoft's new personal finance tool hints at what's in store for next-generation Office applications, from more third-party integrations to subscription requirements.

  • Microsoft Buys Orions Systems To Enhance Vision AI Capabilities in Dynamics 365

    Microsoft announced on Tuesday that it has acquired Orions Systems with the aim of enhancing Dynamics 365 capabilities, as well as the Microsoft Power Platform.

  • Microsoft Hires Movial To Build Android OS for Microsoft Devices

    Microsoft has hired the Romanian operations of software engineering and design services company Movial to develop an Android-based operating system solution for the Microsoft Devices business segment.

  • Microsoft Ending Workflows for SharePoint 2010 Online Next Month

    Microsoft on Monday gave notice that it will be ending support this year for the "workflows" component of SharePoint 2010 Online, as well as deprecating that component for SharePoint 2013 Online.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.