T-SQL Tuesday #025: Trick or …?

Some of the source systems we copy from shard their data across multiple similar tables. For example, instead of having 1 table named dbo.Sales, they might have 30 of them named dbo.Sales_001 to dbo.Sales_030. If there were a rhyme or reason to this sharding it might be seen as an advantage, but unfortunately neither myself nor others on the team have ever found a method to this madness, and the vendor will not divulge the way they do this. As if that's not bad enough, additional tables can pop up out of nowhere, and not all these tables are guaranteed to contain the same columns. Table dbo.Sales_031 may pop up tomorrow and have only a subset of the columns from the previous 30 tables, or may contain new columns not present in any of the others.

To keep this all straight, I have a process that compares the columns in all applicable tables and generates a view that combines their contents using UNION ALL. I've actually blogged about an aspect of this before, but today I have a function containing a nice little trick that's proven very helpful for me. Since the tables I'm generating a view of aren't guaranteed to have the same columns in them, a simple "SELECT *" from each of them won't work because unioning tables together requires identical datatypes in the same order. Instead I have to generate a select statement that explicitly lists all columns for every table. T-SQL can easily accomplish this with cursors and loops, but then I found FOR XML PATH, which made life a lot simpler.

FOR XML PATH has many features which are outside the scope of this post, but the one behavior we're going to exploit today is that you can pass a row tag name that's blank. When you do this, instead of sandwiching each row between tags, it simply concatenates all the row values together into one string. If you add commas, you've got a great way to list out all the columns of a table which you can then form into a SELECT statement. Without any further ado, here's the demo code:

 1-- create our demo table with a few columns
 2CREATE TABLE dbo.MyTable (
 3   ID INT NOT NULL,
 4   Value VARCHAR(20),
 5   Notes NVARCHAR(MAX)
 6);
 7
 8-- select the table's columns from system tables
 9SELECT c.name
10FROM sys.columns c
11INNER JOIN sys.tables t ON c.object_id = t.object_id
12WHERE t.name = 'MyTable'
13ORDER BY c.column_id;
14
15-- now let's format it as XML
16SELECT c.name
17FROM sys.columns c
18INNER JOIN sys.tables t ON c.object_id = t.object_id
19WHERE t.name = 'MyTable'
20ORDER BY c.column_id
21FOR XML PATH;
22
23-- now pass a blank string argument to PATH
24-- note how the <row> tags are now removed but column tags remain
25SELECT c.name
26FROM sys.columns c
27INNER JOIN sys.tables t ON c.object_id = t.object_id
28WHERE t.name = 'MyTable'
29ORDER BY c.column_id
30FOR XML PATH('');
31
32-- casting it to NVARCHAR removes the tags for each column
33DECLARE @s NVARCHAR(MAX);
34
35-- now let's add a blank string argument to PATH
36SET @s = (
37      SELECT ',' + QUOTENAME(c.name) -- comma to separate names
38      FROM sys.columns c
39      INNER JOIN sys.tables t ON c.object_id = t.object_id
40      WHERE t.name = 'MyTable'
41      ORDER BY c.column_id
42      FOR XML PATH('') -- XML that really isn't
43   );
44
45-- remove the leading comma
46SELECT SUBSTRING(@s, 2, LEN(@s));

Wrap the final output with a SELECT and a table name and you're good to go. There's tons more uses for this than creating lists of columns – basically anytime you want to turn column values into a delimited list you can make it happen using this method. Happy coding!