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