compare two tables dynamic- tablo sütunları karşılaştırma

DECLARE @dbname1 nvarchar(48) =N'ERP'

DECLARE @dbname2 nvarchar(48) =N'AERP'

 

DECLARE @TableName nvarchar(48) =N'FATURA'

 

 

SELECT * FROM (

      SELECT

      @dbname1 as DBNAME,

      clmns.name AS [Name],

      usrt.name AS [DataType],

      ISNULL(baset.name, N'') AS [SystemType],

      CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN

      clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

      CAST(clmns.precision AS int) AS [NumericPrecision]

      FROM

      ERP.sys.tables AS tbl

      INNER JOIN ERP.sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

      LEFT OUTER JOIN ERP.sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

      LEFT OUTER JOIN ERP.sys.types AS baset ON baset.user_type_id = clmns.system_type_id and

      baset.user_type_id = baset.system_type_id

      WHERE

      (tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') )as ERP

LEFT JOIN (

      SELECT

      @dbname2 as DBNAME,

      clmns.name AS [Name],

      usrt.name AS [DataType],

      ISNULL(baset.name, N'') AS [SystemType],

      CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN

      clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],

      CAST(clmns.precision AS int) AS [NumericPrecision]

      FROM

      AERP.sys.tables AS tbl

      INNER JOIN AERP.sys.all_columns AS clmns ON clmns.object_id=tbl.object_id

      LEFT OUTER JOIN AERP.sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id

      LEFT OUTER JOIN AERP.sys.types AS baset ON baset.user_type_id = clmns.system_type_id and

      baset.user_type_id = baset.system_type_id

      WHERE

      (tbl.name=@TableName and SCHEMA_NAME(tbl.schema_id)=N'dbo') ) AERP

      ON ERP.Name = AERP.Name

            WHERE

                  ERP.[Length] <> AERP.[Length]