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