Auto-Create Select from Table TSQL Templates
Applicability:
SQL Server 2000: N/A
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
SQL Server 2014: Tested
SQL Server 2016: Tested
SQL Server 2017: Tested
Credits:
Author: Unknown
Date: 10 Jun 2020
Description
For all Tables in a database, generate TSQL templates to SELECT from each table
Code
SELECT qualifiedName as Name, '/* '+qualifiedName+' */'+CHAR(13)+CHAR(10)+REPLACE(
REPLACE(
STUFF(SelectScript, /*delete final comma line-terminator*/
LEN(SelectScript)-CHARINDEX('|,|',
REVERSE(SelectScript)+'|')-1,3
,'')
,'\n',CHAR(13)+CHAR(10))
,'|,|',',') /*put in new-lines and convert token to comma*/ as Template
FROM (SELECT
so.name AS Name,
OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,
'SELECT '+REPLACE(COALESCE(
(
SELECT '\n '+QUOTENAME(sp.name)+'|,|'+COALESCE(' /*'
+CONVERT(VARCHAR(MAX),value)+'*/','')
FROM sys.columns sp
LEFT OUTER JOIN sys.extended_properties ep
ON sp.object_id = ep.major_ID
AND sp.column_ID = minor_ID AND class=1
WHERE sp.object_ID=so.object_ID
AND column_ID>0
ORDER BY column_ID
FOR XML PATH('')
),'1')
,',||', '')
+'\nFROM '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))+'.'
+QUOTENAME(so.name)+COALESCE(' /*'
+CONVERT(VARCHAR(300), value)+'*/', '') [SelectScript]
FROM
sys.objects so
LEFT OUTER JOIN sys.extended_properties ep
/* get any extended properties */
ON ep.name LIKE 'MS_Description'
AND major_ID=so.object_ID
AND minor_ID=0
WHERE
OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f
ORDER BY name