Compare Page Revisions
« Older Revision - Back to Page History - Current Revision
dbo.DbLayout
declare @TableNameExt nvarchar(500) = '[dbo].[OrderHeader]' ,@IdentityInsert bit = 0 ;with cte as ( select * ,TableNameExt = '[' + SchemaName + '].[' + TableName + ']' ,ColumnNameExt = '[' + ColumnName + ']' ,MaxLen = max(len(ColumnName)) over (partition by SchemaName, TableName) + 2 from dbo.DbLayout where 1=1 and '[' + SchemaName + '].[' + TableName + ']' = @TableNameExt ) select distinct TableNameExt ,Sort1 = 20 ,Sort2 = 10 ,SqlCode = '/*--- ' + TableNameExt + ' ---*/' from cte union select distinct TableNameExt ,Sort1 = 30 ,Sort2 = 10 ,SqlCode = 'set identity_insert ' + TableNameExt + ' on' from cte where 1=1 and cte.IsIdentity = 1 and @IdentityInsert = 1 union select distinct TableNameExt ,Sort1 = 35 ,Sort2 = 10 ,SqlCode = '' from cte where 1=1 and cte.IsIdentity = 1 and @IdentityInsert = 1 union select distinct TableNameExt ,Sort1 = 40 ,Sort2 = 10 ,SqlCode = 'insert into ' + TableNameExt + ' (' from cte union select distinct TableNameExt ,Sort1 = 50 ,Sort2 = ColumnOrder ,SqlCode = ' ' + case when (row_number() over (order by ColumnOrder)) = 1 then ' ' else ',' end + ColumnNameExt from cte where 1=1 and (cte.IsIdentity = 0 or @IdentityInsert = 1) union select distinct TableNameExt ,Sort1 = 55 ,Sort2 = 10 ,SqlCode = ' )' from cte union select distinct TableNameExt ,Sort1 = 60 ,Sort2 = 10 ,SqlCode = 'select' from cte union select distinct TableNameExt ,Sort1 = 70 ,Sort2 = ColumnOrder ,SqlCode = ' ' + case when (row_number() over (order by ColumnOrder)) = 1 then ' ' else ',' end + ColumnNameExt + REPLICATE(' ', MaxLen - len(ColumnNameExt) + 2) + '= ' + case ColumnName when 'CreatedBy' then '''system''' when 'UpdatedBy' then '''system''' when 'CreatedOn' then 'getutcdate()' when 'UpdatedOn' then 'getutcdate()' else 'null'+ ' -- ' + cte.DataType + ' ' + case when cte.IsNullable = 1 then '' else 'not ' end + 'null' end from cte where 1=1 and (cte.IsIdentity = 0 or @IdentityInsert = 1) union select distinct TableNameExt ,Sort1 = 75 ,Sort2 = 10 ,SqlCode = '' from cte union select distinct TableNameExt ,Sort1 = 80 ,Sort2 = 10 ,SqlCode = 'set identity_insert ' + TableNameExt + ' off' from cte where 1=1 and cte.IsIdentity = 1 and @IdentityInsert = 1 union select distinct TableNameExt ,Sort1 = 90 ,Sort2 = 10 ,SqlCode = '' from cte where 1=1 and cte.IsIdentity = 1 order by TableNameExt ,Sort1
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.