A quick tip for dynamic SQL

Anyone that's ever worked with dynamic SQL before knows it can get really messy and hard to read and maintain - which is why before actually executing the dynamic SQL, it's best to first just print it just so you could check it visually to make sure everything looks alright before executing it - Here's where this tip comes in handy: Add couple of variables to your script - one for line breaks and one for tabs, and use them when building your dynamic SQL - so when you finally print it, it's going to be nicely formatted and easy to read.

This code example is in T-SQL, but of course this tip can be used in any rdbms:

DECLARE @Sql nvarchar(max), 
    @Tab nchar(1) = NCHAR(9),
    @LineBreak nchar(2) = NCHAR(13) + NCHAR(10),
    @ColumnSeperaor nchar(5);

SET @ColumnSeperaor = N',' + @LineBreak + @Tab + @Tab;

SELECT @Sql = N'SELECT  '+ STRING_AGG(QUOTENAME(@tableName) + N'.'+ QUOTENAME(Column_name), @ColumnSeperaor) + @LineBreak
FROM Information_Schema.Columns
WHERE Table_Name = @TableName

SELECT @Sql = @Sql + N'FROM '+ QUOTENAME(@TableName)

PRINT @SQL 

This will result with a nicely formatted, readable SQL such as:

SELECT [TableName].[Col1], 
       [TableName].[Col2], 
       [TableName].[Col3] 
FROM [TableName]

This was also edited into my blog post entitled The do’s and don’ts of dynamic SQL for SQL Server