Dynamic Sql formatting
/* Bad @tsql formating */
DECLARE @tsql nvarchar(max);
DECLARE @sep nvarchar(30) = ' UNION ALL ';
DECLARE @debug bit = 1;
SELECT @tsql = COALESCE(@tsql, N'') +
N'SELECT N' + QUOTENAME(name,'''') +
N' AS DBName, (SELECT COUNT(*) FROM ' +
QUOTENAME(name) + N'.sys.tables) AS TableCount' +
@sep
FROM sys.databases
ORDER BY name;
SET @tsql = LEFT(@tsql, LEN(@tsql) - LEN(@sep));
IF @debug = 1 SELECT @tsql AS "tsql" ELSE EXEC sp_executesql @tsql;
/* Good @tsql formating */
DECLARE @tsql nvarchar(max);
DECLARE @sep nvarchar(30) = ' UNION ALL ';
DECLARE @debug bit = 1;
DECLARE @crlf nvarchar(10) = NCHAR(13) + NCHAR(10);
SELECT @tsql = COALESCE(@tsql, N'') + @crlf +
N'SELECT N' + QUOTENAME(name,'''') + N' AS DBName' + @crlf +
N' , (SELECT COUNT(*) FROM ' + QUOTENAME(name) + N'.sys.tables) AS TableCount' + @crlf +
@sep
FROM sys.databases
ORDER BY name;
SET @tsql = LEFT(@tsql, LEN(@tsql) - LEN(@sep)) + N';';
IF @debug = 1 SELECT @tsql AS "tsql" ELSE EXEC sp_executesql @tsql;