Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

Auto-Create Update each 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 UPDATE each table.

Code



SELECT '/* '+qualifiedName+' */' +CHAR(13)+CHAR(10)+REPLACE( REPLACE( REPLACE( REPLACE( STUFF(UpdateScript, /*delete final comma line-terminator*/ LEN(UpdateScript)-1-CHARINDEX('|,|', REVERSE(UpdateScript)+'|'),3 ,'') ,'\n',CHAR(13)+CHAR(10)) ,'|,|',',') /*put in new-lines and convert token to comma*/ ,'&lt;','<') ,'&gt;','>') AS Template FROM (SELECT so.name AS Name, OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName, 'UPDATE '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID)) +'.'+QUOTENAME(so.name) +COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '') +'\nSET ' +REPLACE(COALESCE( ( SELECT REPLACE ('\n '+sp.name+' = '+'|delim;<' +REPLACE( COALESCE( CONVERT(VARCHAR(MAX),value) ,REPLACE(sp.name,'@','') ) ,',','') +','+t.name+','+'>|delim;'+'|,|' +COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'|delim;', CASE WHEN t.name IN ('char', 'varchar','nchar' ,'nvarchar','text','ntext' ,'datetime','date') THEN '''' WHEN t.precision=0 THEN '''' ELSE '' END) FROM sys.columns sp INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID 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') ,',||', '') + CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1 THEN '\nWHERE '+SUBSTRING( ( SELECT '\n AND '+ quotename(COL_NAME(ic.object_id,ic.column_id)) +'='+REPLACE('&delim;<' +REPLACE( COALESCE( CONVERT(VARCHAR(MAX),value) ,t.name), ',','') +','+t.name+','+'>&delim;' +COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'&delim;', CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar', 'text','ntext','datetime','date') THEN '''' WHEN t.precision=0 THEN '''' ELSE '' END) FROM sys.index_columns AS ic inner join sys.indexes i on ic.index_ID=i.index_ID and ic.object_ID=i.object_ID inner join sys.columns sp on sp.column_ID= ic.column_ID and sp.object_ID=ic.object_ID INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID LEFT OUTER JOIN sys.extended_properties ep /* get any extended properties */ ON ep.name LIKE 'MS_Description' AND major_ID=ic.object_ID AND minor_ID=sp.column_ID and type=1 AND class=1 WHERE is_primary_key=1 AND so.object_id=ic.object_id ORDER BY key_ordinal FOR XML PATH('') ),11,8000) ELSE '' END [UpdateScript] 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 --for the table WHERE OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f ORDER BY name





ċ
Andy Hughes,
Jun 10, 2020, 5:25 PM
Comments