Auto-Create Select from Table TSQL Templates

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