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_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!