USE Diablo
GO
SET QUOTED_IDENTIFIER OFF
DECLARE @SQL VARCHAR(MAX) = ''
SELECT
@SQL =
(SELECT
"SELECT
" + col.ColSQL + "FROM " + t.name + "
"
FROM sys.tables t CROSS APPLY
(SELECT
(SELECT
CASE WHEN ty.name LIKE '%CHAR' THEN
" ,CASE WHEN " + c.name + " IS NULL THEN '1' ELSE 0 END AS " + c.name + "_NULL
,CASE WHEN LEN(RTRIM((" + c.name + "))) = 0 THEN '1' ELSE '0' END AS " + c.name + "_EMPTY
,CASE WHEN LEN(RTRIM((" + c.name + "))) > 0 THEN '1' ELSE '0' END AS " + c.name + "_POPLT
"
ELSE
" ,CASE WHEN " + c.name + " IS NULL THEN 1 ELSE 0 END AS " + c.name + "_NULL
,CASE WHEN " + c.name + " = 0 THEN 1 ELSE 0 END AS " + c.name + "_EMPTY
,CASE WHEN " + c.name + " != 0 THEN 1 ELSE 0 END AS " + c.name + "_POPLT
"
END
FROM sys.columns c
INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE c.OBJECT_ID = t.OBJECT_ID
ORDER BY c.column_id
FOR XML PATH('')) AS ColSQL) col
FOR XML PATH(''))
SET @SQL = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SQL, ' ', CHAR(20)), '&', '&'), '<', '<'), '>','>'), '
',CHAR(10)), '
', CHAR(13)), ' ', CHAR(9))
--EXECUTE SQL
-- EXEC(@SQL)
--PRINT SQL
SELECT
1 AS tag,
NULL AS parent,
'
' + @SQL + '
' AS [Q!1!Q!CDATA]
FOR XML EXPLICIT