Credits:
Description
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 |