Compare Two Tables and Columns - Meta-data T-SQL script

You need a tool to compare the meta- data between two tables, and you don't have the budget for one of the great tools already out in the market.  The script below will display all the differences between two tables for the main table and column meta-data attributes.

       
----------------------------------------------------------------------------
-- the two databases to be compared
-- user data entered here:
----------------------------------------------------------------------------
declare  @MyLegacyDatabase  nvarchar(128)  = N'[MyLegacyDatabase]' 
        ,@MyProposedDatabase nvarchar(128) = N'[MycompareDatabase]' 
        ,@Objecttype   nchar(1)    = N'U' ; 

----------------------------------------------------------------------------
-- variables and setup
----------------------------------------------------------------------------
declare @LegacyQuery nvarchar(max) = '', @ProposedQuery nvarchar(max) = '';
select  @LegacyQuery = 
  'select  Tablename = o.[name], Columnname =  c.[name], c.[column_id], cast(t.name as varchar(15)) as datatype, c.system_type_id, c.user_type_id, c.max_length, c.[precision], c.scale '
 +   ' from ' + @MyLegacyDatabase + '.sys.all_columns as c '
 +   ' inner join ' + @MyLegacyDatabase + '.sys.all_objects as o on c.object_id = o.object_id and o.type = '''  + @Objecttype + ''' and o.is_ms_shipped = 0 '
 +   ' inner join ' + @MyLegacyDatabase + '.sys.systypes as t  on c.system_type_id = t.xtype; ';
select  @ProposedQuery = 
  'select  Tablename = o.[name], Columnname =  c.[name], c.[column_id], cast(t.name as varchar(15)) as datatype, c.system_type_id, c.user_type_id, c.max_length, c.[precision], c.scale '
 +   ' from ' + @MyProposedDatabase + '.sys.all_columns as c '
 +   ' inner join ' + @MyProposedDatabase + '.sys.all_objects as o on c.object_id = o.object_id and o.type = '''  + @Objecttype + ''' and o.is_ms_shipped = 0 '
 +   ' inner join ' + @MyProposedDatabase + '.sys.systypes as t  on c.system_type_id = t.xtype; ';
select @LegacyQuery;  

declare @tblLegacycompare TABLE ( [Tablename] [sysname] NULL, [Columnname] [nvarchar](128) NULL, [column_id] int null, [datatype] [varchar](15) NULL, [system_type_id] [tinyint] NULL, [user_type_id] [int] NULL, [max_length] [smallint] NULL, [precision] [tinyint] NULL, [scale] [tinyint] NULL);
declare @tblProposedcompare TABLE ( [Tablename] [sysname] NULL, [Columnname] [nvarchar](128) NULL, [column_id] int null, [datatype] [varchar](15) NULL, [system_type_id] [tinyint] NULL, [user_type_id] [int] NULL, [max_length] [smallint] NULL, [precision] [tinyint] NULL, [scale] [tinyint] NULL);

--select top 1 * from @tblLegacycompare; 
--EXEC sp_executesql @LegacyQuery;
--select @LegacyQuery;   

----------------------------------------------------------------------------
-- populate the tables with data to compare
----------------------------------------------------------------------------
           
insert into @tblLegacycompare (Tablename, Columnname, column_id, datatype, system_type_id, user_type_id, max_length, [precision], scale) 
 EXEC sp_executesql @LegacyQuery;
 -- select * from @tblLegacycompare;

insert into @tblProposedcompare (Tablename, Columnname, column_id, datatype, system_type_id, user_type_id, max_length, [precision], scale) 
 EXEC sp_executesql @ProposedQuery;
 --  select * from @@tblProposedcompare;

----------------------------------------------------------------------------
-- compare
----------------------------------------------------------------------------

select TableSort = isnull(n.Tablename ,o.Tablename ), ColumnSort = isnull(n.Columnname, o.Columnname)
,OnLegacyDb  = case when  o.Tablename is not null then 'Yes' else 'No' end 
,OnProposedDb = case when  n.Tablename is not null then 'Yes' else 'No' end 
,ColumnAddDrop = case when o.Columnname = n.Columnname then '-'      when o.Columnname is null and n.Columnname is not null then 'ADD' when o.Columnname is not null and n.Columnname is null then 'DROP' else '???' end
,ColumnIDChg   = case when o.column_id  = n.column_id  then 'no chg' when o.column_id  is null and n.column_id  is not null then 'ADD' when o.column_id  is not null and n.column_id  is null then 'DROP' else 'CHANGE' end


,ColumnTypeChg = case when isnull(o.datatype,'') = 'varchar' and isnull(n.datatype,'') = 'char' and isnull(o.max_length,-1) = isnull(n.max_length,-2) then 'no chg(H)'
      when o.datatype is null then 'add' when n.datatype is null then 'drop' when  o.datatype != n.datatype then 'CHANGE' else 'no chg' end

,ColumnLengthChg = case when o.max_length  is null then 'add' when n.max_length  is null then 'drop' when o.max_length  != n.max_length  then 'CHANGE' else 'no chg' end
,ColumnPrecisionChg = case when o.[precision] is null then 'add' when n.[precision] is null then 'drop' when o.[precision] != n.[precision] then 'CHANGE' else 'no chg' end
,ColumnScaleChg  = case when o.scale    is null then 'add' when n.scale  is null then 'drop' when o.scale       != n.scale  then 'CHANGE' else 'no chg' end
-- ,o.Tablename as TblOld, n.Tablename as TblNew, o.Columnname as ColOld, n.Columnname as ColNew
, o.datatype as TypeOld, n.datatype as TypeNew, o.column_id as ColIDOld, n.column_id as ColIDNew
, o.max_length as LENold, n.max_length as LENnew, o.[precision] as PrecisionOld, n.[precision] as PrecisionNew, o.scale as ScaleOld, n.scale as ScaleNew
, o.system_type_id as SysTypeOld, o.user_type_id as UserTypeOld, n.system_type_id as SysTypeNew, n.user_type_id as UserTypeNew

from @tblLegacycompare as o full outer join @tblProposedcompare as n on o.Tablename = n.Tablename and o.Columnname = n.Columnname

order by isnull(n.Tablename ,o.Tablename ) asc , isnull(n.Columnname, o.Columnname) asc ;