Compare Page Revisions
« Older Revision - Back to Page History - Current Revision
SELECT DISTINCT TABLE_NAME = TBL_DEV.NAME, COLUMN_NAME = COL_DEV.NAME, STATUS = CASE -- New Columns ------------------------------------------------------------------------ WHEN NOT EXISTS (SELECT 2 FROM FEDRPL.DBO.SYSOBJECTS TBL_PROD INNER JOIN FEDRPL.DBO.SYSCOLUMNS COL_PROD ON TBL_PROD.ID = COL_PROD.ID WHERE 1=1 AND TBL_PROD.TYPE = 'U' AND TBL_PROD.NAME = TBL_DEV.NAME AND COL_PROD.NAME = COL_DEV.NAME ) THEN 'New Column' -- Changed Column --------------------------------------------------------------------- WHEN NOT EXISTS (SELECT 2 FROM FEDRPL.DBO.SYSOBJECTS TBL_PROD INNER JOIN FEDRPL.DBO.SYSCOLUMNS COL_PROD ON TBL_PROD.ID = COL_PROD.ID WHERE 1=1 AND TBL_PROD.NAME = TBL_DEV.NAME AND COL_PROD.NAME = COL_DEV.NAME AND COL_PROD.XTYPE = COL_DEV.XTYPE AND COL_PROD.LENGTH = COL_DEV.LENGTH AND COL_PROD.PREC = COL_DEV.PREC AND COALESCE(COL_PROD.SCALE,-1) = COALESCE(COL_DEV.SCALE,-1) AND COL_PROD.ISNULLABLE = COL_DEV.ISNULLABLE AND COL_PROD.STATUS & 128 = COL_DEV.STATUS & 128 ) THEN 'Changed Column Type, Size, Prec, Scale, Nullability, or Identity' ELSE '' END, DATA_TYPE = TYP_DEV.NAME, DATA_SIZE = COL_DEV.LENGTH, DATA_PREC = COL_DEV.PREC, DATA_SCALE = coalesce(COL_DEV.SCALE,-1) , COL_DEV.ISNULLABLE, IDENTITY_COL = CASE WHEN COL_DEV.STATUS & 128 > 0 THEN 1 ELSE 0 END, PK_FLAG = CASE WHEN COL_DEV.COLID IN (SELECT K.COLID FROM FEDRPL_DEV.DBO.SYSINDEXES I INNER JOIN FEDRPL_DEV.DBO.SYSINDEXKEYS K ON I.INDID = K.INDID AND I.ID = K.ID INNER JOIN FEDRPL_DEV.DBO.SYSOBJECTS O2 ON I.ID = O2.ID INNER JOIN FEDRPL_DEV.DBO.SYSCOLUMNS C2 ON K.ID = C2.ID AND K.COLID = C2.COLID WHERE (I.STATUS & 0x800) = 0x800 AND O2.ID = TBL_DEV.ID) THEN 1 ELSE 0 END FROM FEDRPL_DEV.DBO.SYSOBJECTS TBL_DEV INNER JOIN FEDRPL_DEV.DBO.SYSCOLUMNS COL_DEV ON TBL_DEV.ID = COL_DEV.ID INNER JOIN FEDRPL_DEV.DBO.SYSTYPES TYP_DEV ON TYP_DEV.XTYPE = COL_DEV.XTYPE WHERE 1=1 AND TBL_DEV.TYPE = 'U' AND TYP_DEV.NAME NOT IN ('SYSNAME') AND TBL_DEV.NAME NOT IN ('dtproperties') AND EXISTS (SELECT 2 FROM FEDRPL.DBO.SYSOBJECTS TBL_PROD WHERE TYPE = 'U' AND NAME = TBL_DEV.NAME) AND NOT EXISTS (SELECT 2 FROM FEDRPL.DBO.SYSOBJECTS TBL_PROD INNER JOIN FEDRPL.DBO.SYSCOLUMNS COL_PROD ON TBL_PROD.ID = COL_PROD.ID WHERE 1=1 AND TBL_PROD.NAME = TBL_DEV.NAME AND COL_PROD.NAME = COL_DEV.NAME AND COL_PROD.XTYPE = COL_DEV.XTYPE AND COL_PROD.LENGTH = COL_DEV.LENGTH AND COL_PROD.PREC = COL_DEV.PREC AND COALESCE(COL_PROD.SCALE,-1) = COALESCE(COL_DEV.SCALE,-1) AND COL_PROD.ISNULLABLE = COL_DEV.ISNULLABLE AND COL_PROD.STATUS & 128 = COL_DEV.STATUS & 128 ) ORDER BY TBL_DEV.NAME--, COL_DEV.COLORDER
select distinct ObjectName = '[' + a.SchemaName + '].[' + a.TableName + ']' ,Diff = 'Table not deployed' from AAA_Dev.dbo.DbLayout a where 1=1 and a.TableName not in (select TableName from AAA.dbo.DbLayout) union select ObjectName = '[' + a.SchemaName + '].[' + a.TableName + '].[' + a.ColumnName + ']' ,Diff = case when b.SchemaName is null then 'Column not deployed' else 'Column changed: ' + case when a.DataType <> b.DataType then 'DataType;' else '' end + case when a.IsNullable <> b.IsNullable then 'IsNullable;' else '' end + case when a.IsIdentity <> b.IsIdentity then 'IsIdentity;' else '' end + case when a.IdentityDesc <> b.IdentityDesc then 'IdentityDesc;' else '' end + case when a.IsPkey <> b.IsPkey then 'IsPkey;' else '' end end from AAA_Dev.dbo.DbLayout a left join AAA.dbo.DbLayout b on a.SchemaName = b.SchemaName and a.TableName = b.TableName and a.ColumnName = b.ColumnName where 1=1 and a.TableName not like '%$%' and a.TableName in (select TableName from AAA.dbo.DbLayout) and ( 1=2 or b.SchemaName is null or a.DataType <> b.DataType or a.IsNullable <> b.IsNullable or a.IsIdentity <> b.IsIdentity or a.IdentityDesc <> b.IdentityDesc or a.IsPkey <> b.IsPkey )
with SourceDbLayout as ( select distinct DatabaseName = db_name() ,SchemaName = s.name ,TableName = o.name ,ColumnName = c.name ,DataType = case when t.name in ('char','text','varchar') then t.name + '(' + convert(varchar,c.max_length) + ')' when t.name in ('nchar','ntext','nvarchar') then t.name + '(' + convert(varchar,c.max_length/2) + ')' when t.name in ('decimal','numeric','real') then t.name + '(' + convert(varchar,c.precision) + ',' + convert(varchar,c.scale) + ')' else t.name end ,IsNullable = c.is_nullable ,IsIdentity = c.is_identity ,IdentityDesc = case when c.is_identity = 0 then '' else '(' + convert(varchar(50), IDENT_SEED(o.name)) + ', ' + convert(varchar(50), IDENT_INCR(o.name)) + ')' end ,IsPkey = case when c.column_id in (select k.colid from sysindexes i inner join sysindexkeys k on i.indid = k.indid and i.id = k.id inner join sysobjects o2 on i.id = o2.id inner join syscolumns c2 on k.id = c2.id and k.colid = c2.colid where (i.status & 0x800) = 0x800 and o2.id = o.object_id) then 1 else 0 end ,ColumnOrder = c.column_id ,DefaultValue = coalesce((select definition from sys.default_constraints where object_id = c.default_object_id), '') ,ColumnDesc = coalesce(( select value from fn_listextendedproperty(NULL, 'schema', s.name, 'table', o.name, 'column', c.name) where name='MS_Description' ), '') from SourceDatabaseName.sys.objects o inner join SourceDatabaseName.sys.columns c on o.object_id = c.object_id inner join SourceDatabaseName.sys.types t on t.system_type_id = c.system_type_id inner join SourceDatabaseName.sys.schemas s on o.schema_id = s.schema_id where 1=1 and o.type in ('u','v') and t.name not in ('sysname') and o.name not in ('dtproperties') ) /*===============================================================================================*/ ,TargetDbLayout as ( select distinct DatabaseName = db_name() ,SchemaName = s.name ,TableName = o.name ,ColumnName = c.name ,DataType = case when t.name in ('char','text','varchar') then t.name + '(' + convert(varchar,c.max_length) + ')' when t.name in ('nchar','ntext','nvarchar') then t.name + '(' + convert(varchar,c.max_length/2) + ')' when t.name in ('decimal','numeric','real') then t.name + '(' + convert(varchar,c.precision) + ',' + convert(varchar,c.scale) + ')' else t.name end ,IsNullable = c.is_nullable ,IsIdentity = c.is_identity ,IdentityDesc = case when c.is_identity = 0 then '' else '(' + convert(varchar(50), IDENT_SEED(o.name)) + ', ' + convert(varchar(50), IDENT_INCR(o.name)) + ')' end ,IsPkey = case when c.column_id in (select k.colid from sysindexes i inner join sysindexkeys k on i.indid = k.indid and i.id = k.id inner join sysobjects o2 on i.id = o2.id inner join syscolumns c2 on k.id = c2.id and k.colid = c2.colid where (i.status & 0x800) = 0x800 and o2.id = o.object_id) then 1 else 0 end ,ColumnOrder = c.column_id ,DefaultValue = coalesce((select definition from sys.default_constraints where object_id = c.default_object_id), '') ,ColumnDesc = coalesce(( select value from fn_listextendedproperty(NULL, 'schema', s.name, 'table', o.name, 'column', c.name) where name='MS_Description' ), '') from TargetDatabaseName.sys.objects o inner join TargetDatabaseName.sys.columns c on o.object_id = c.object_id inner join TargetDatabaseName.sys.types t on t.system_type_id = c.system_type_id inner join TargetDatabaseName.sys.schemas s on o.schema_id = s.schema_id where 1=1 and o.type in ('u','v') and t.name not in ('sysname') and o.name not in ('dtproperties') ) /*===============================================================================================*/ select distinct ObjectName = '[' + a.SchemaName + '].[' + a.TableName + ']' ,Diff = 'Table not deployed' from SourceDbLayout a where 1=1 and a.TableName not in (select TableName from TargetDbLayout) union select ObjectName = '[' + a.SchemaName + '].[' + a.TableName + '].[' + a.ColumnName + ']' ,Diff = case when b.SchemaName is null then 'Column not deployed' else 'Column changed: ' + case when a.DataType <> b.DataType then 'DataType;' else '' end + case when a.IsNullable <> b.IsNullable then 'IsNullable;' else '' end + case when a.IsIdentity <> b.IsIdentity then 'IsIdentity;' else '' end + case when a.IdentityDesc <> b.IdentityDesc then 'IdentityDesc;' else '' end + case when a.IsPkey <> b.IsPkey then 'IsPkey;' else '' end end from SourceDbLayout a left join TargetDbLayout b on a.SchemaName = b.SchemaName and a.TableName = b.TableName and a.ColumnName = b.ColumnName where 1=1 and a.TableName not like '%$%' and a.TableName in (select TableName from TargetDbLayout) and ( 1=2 or b.SchemaName is null or a.DataType <> b.DataType or a.IsNullable <> b.IsNullable or a.IsIdentity <> b.IsIdentity or a.IdentityDesc <> b.IdentityDesc or a.IsPkey <> b.IsPkey )
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.