T-SQL Tuesday #104: Code I Would Hate to Live Without

T-SQL Tuesday is a great monthly tradition where a bunch of people in the community can contribute to a blog party along a general (or sometimes specific) topic. This month, Bert Wagner invites us to share our most cherished body of work:

For this month’s T-SQL Tuesday, I want you to write about code you’ve written that you would hate to live without.

I’ve written a lot of code over the years. To show for it, I have plenty of blog posts (here, on SQLPerformance.com, and on SQLBlog.org), 3,000+ answers on Stack Overflow, 2,000+ answers on Database Administrators, and over 100 tips on MSSQLTips.com. I end up borrowing from my own examples from time to time; occasionally I come across it accidentally, after searching and having forgotten I wrote the thing in the first place. Not all of those involve code explicitly, but you can see how I might lose track of things once in a while.

None of these things is all that hard to re-write; once you have the concepts down for things, most of the tedium is syntax. There is, however, one piece of reusable code that I go back to again and again.

Comparing System Metadata

One of the areas I like to focus on is new features in SQL Server. Under both MVP and Microsoft Partner programs, I get to see a lot of builds of SQL Server that don’t make it to the public, and documentation for these builds is typically sparse. In order to get a head start on testing things out, I often need to explore on my own. And so I wrote some scripts for that, which I’ve talked about in previous blog posts:

• How I spot not-yet-documented features in SQL Server CTPs
• More ways to discover changes in new versions of SQL Server

When I install a new version of SQL Server (be it a cumulative update, the final service pack for a major version, or the first CTP of vNext), there are two steps:

1. Create a linked server to the build that came before it
2. Create local synonyms referencing the important catalog views in the linked server

Assume I have just installed CTP 1 of vNext, and I want to compare that to an existing SQL Server 2017 instance:

USE [master]; -- on .\CTP1
 
DECLARE @previous sysname = N'.\SQL2017';
 
EXEC dbo.sp_addlinkedserver   @server     = @previous, @srvproduct = N'SQL Server';
EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname = @previous, @useself    = N'True';
EXEC dbo.sp_serveroption      @server     = @previous,
     @optname = N'collation compatible',  @optvalue = N'true';
EXEC dbo.sp_serveroption      @server     = @previous,
     @optname = N'data access',           @optvalue = N'true';

Next I create the synonyms, such as:

CREATE SYNONYM dbo.OlderCTP_all_objects     FOR [.\SQL2017].master.sys.all_objects;
CREATE SYNONYM dbo.OlderCTP_all_columns     FOR [.\SQL2017].master.sys.all_columns;
CREATE SYNONYM dbo.OlderCTP_all_sql_modules FOR [.\SQL2017].master.sys.all_sql_modules;
...

I can then do all kinds of things to see what objects are new, what columns have been added, how catalog views have changed, and a whole slew of other things I explain in the previous posts.

There are tools that can be bent and massaged to help with this type of comparison, of course, and I do need to give DBA xPress a thorough try. But in the meantime, I like being able to fully control both the output and the join criteria.

Anyway, this has been a very useful method, and I’ll let you guess why it came to mind this week. 🙂

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.