Tuesday, November 29, 2011

Compare two database table definition

Below is code for comparing two database table.
 
   1:  declare @pTableNameName varchar(500) = 'EP50000'
   2:   
   3:  select 
   4:      
   5:      T.TableName,S.TableName
   6:  from
   7:      (
   8:          select 
   9:              O.name TableName,
  10:              C.name ColumnName,
  11:              ROW_NUMBER() over(partition by O.name order by O.name,C.column_id) ColumnNumber,
  12:              COUNT(*) over(partition by O.name) ColumnCount
  13:          from 
  14:              ANGEL.sys.objects O inner join
  15:              sys.columns C on C.object_id = O.object_id
  16:                  and O.type = 'U'
  17:          where
  18:              O.name = @pTableNameName
  19:      )T
  20:      inner join
  21:      (
  22:          select 
  23:              O.name TableName,
  24:              C.name ColumnName,
  25:              ROW_NUMBER() over(partition by O.name order by O.name,C.column_id) ColumnNumber,
  26:              COUNT(*) over(partition by O.name) ColumnCount
  27:          from 
  28:              ANGEL_SSRS.sys.objects O inner join
  29:              sys.columns C on C.object_id = O.object_id
  30:                  and O.type = 'U'
  31:      )S on T.ColumnName = S.ColumnName 
  32:          and T.ColumnNumber = s.ColumnNumber
  33:          and T.ColumnCount = s.ColumnCount
  34:  group by
  35:      T.TableName,
  36:      S.TableName,
  37:      T.ColumnCount
  38:  having
  39:      COUNT(T.ColumnNumber) = T.ColumnCount