Compare column names in two tables, in two databases in SQL

user_15172193 0
Viewed 60

I am trying to compare columns in two tables in SQL using following code;

DECLARE @Table1 VARCHAR(2048) = 'Table1',
        @Table2 VARCHAR(2048) = 'Tbale2'

-- Table 1 Columns into #temp_table_1
SELECT DISTINCT
       a.COLUMN_NAME AS [Column Name],
       a.DATA_TYPE AS [Data Type],
       a.CHARACTER_MAXIMUM_LENGTH,
       a.NUMERIC_PRECISION,
       a.NUMERIC_SCALE
INTO #temp_table1
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE a.TABLE_NAME = @Table1
ORDER BY a.COLUMN_NAME

-- Table 2 Columns into #temp_table_2
SELECT DISTINCT
       a.COLUMN_NAME AS [Column Name],
       a.DATA_TYPE AS [Data Type],
       a.CHARACTER_MAXIMUM_LENGTH,
       a.NUMERIC_PRECISION,
       a.NUMERIC_SCALE
INTO #temp_table2
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE a.TABLE_NAME = @Table2
ORDER BY a.COLUMN_NAME

SELECT 
    @Table1 [Table 1],
    isnull(t1.[Column Name],'') [Table 1 Column Name],
    isnull(t2.[Column Name],'') [Table 2 Column Name],
    @Table2 [Table 2],
    isnull(t1.[Data Type],'') [Table 1 Column Type],
    isnull(t2.[Data Type],'') [Table 2 Column Type],
    isnull(cast(t1.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t1.NUMERIC_PRECISION as varchar(50)),'') + ',' +
        isnull(cast(t1.NUMERIC_SCALE as varchar(50)),'')) [Table 1 Column Precision],
    isnull(cast(t2.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t2.NUMERIC_PRECISION as varchar(50)),'') + ',' +
        isnull(cast(t2.NUMERIC_SCALE as varchar(50)),'')) [Table 2 Column Precision],
    --[Data Type Warning]
        case when isnull(t1.[Column Name],'') = isnull(t2.[Column Name],'') 
                and (
                     isnull(t1.[Data Type],'') <> isnull(t2.[Data Type],'')
                     or
                     isnull(cast(t1.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t1.NUMERIC_PRECISION as varchar(50)),'') + ',' +
                            isnull(cast(t1.NUMERIC_SCALE as varchar(50)),''))
                        <> 
                     isnull(cast(t2.CHARACTER_MAXIMUM_LENGTH as varchar(50)),isnull(cast(t2.NUMERIC_PRECISION as varchar(50)),'') + ',' +
                            isnull(cast(t2.NUMERIC_SCALE as varchar(50)),''))
                     )
            then '*** Data Type Mismatch ***' else '' end 
    [Data Type Warning]
FROM #temp_table1 t1 
FULL OUTER JOIN #temp_table2 t2 ON t1.[Column Name] = t2.[Column Name]
WHERE 1=1

DROP TABLE #temp_table1, #temp_table2

My two tables are located in separate databases within same SQL Server. Hence, upon executing above code either my "table1" or "table2"contents will get retrieved. I will have to switch between the databases to see each table contents.

How can I retrieve both table information without switching databases?

Any help would be appreciated.

sql-server sql
2 Answers

You can use the full name of the table, including the name of the database (and even the server) like this:

..FROM [YouDataBaseName].[INFORMATION_SCHEMA].[COLUMNS]
user_119885130

To access system tables from another database you can prefix the database name, eg DB1.sys.tables.

  • Don't use INFORMATION_SCHEMA, it's for compatibility only. Use sys.tables and sys.columns.
  • Temp tables are just over-complicating this, they are unnecessary.
  • DISTINCT is unnecessary as every column name is unique.
  • Use the sysname data type for variables containing object or column names.
  • Use EXISTS (SELECT ... EXCEPT SELECT ...) to compare with nulls.
DECLARE @Table1 sysname = 'Table1',
        @Table2 sysname = 'Tbale2'

SELECT 
    @Table1 [Table 1],
    ISNULL(t1.name, '') [Table 1 Column Name],
    ISNULL(t2.name, '') [Table 2 Column Name],
    @Table2 [Table 2],
    ISNULL(t1.DataType, '') [Table 1 Column Type],
    ISNULL(t2.DataType, '') [Table 2 Column Type],
    ISNULL(CAST(t1.max_length as varchar(50)), ISNULL(CAST(t1.precision as varchar(50)), '') + ',' +
        ISNULL(CAST(t1.scale as varchar(50)), '')) [Table 1 Column Precision],
    ISNULL(CAST(t2.max_length as varchar(50)), ISNULL(CAST(t2.precision as varchar(50)), '') + ',' +
        ISNULL(CAST(t2.scale as varchar(50)), '')) [Table 2 Column Precision],
    --[Data Type Warning]
    CASE WHEN t1.name = t2.name
              AND EXISTS (
                  SELECT t1.DataType, t1.max_length, t1.precision, t1.scale
                  EXCEPT
                  SELECT t2.DataType, t2.max_length, t2.precision, t2.scale
              )
            THEN '*** Data Type Mismatch ***' ELSE '' END [Data Type Warning]
FROM (
    SELECT c.*, tp.name DataType
    FROM DB1.sys.tables t
    JOIN DB1.sys.columns c ON c.object_id = t.object_id
    JOIN DB1.sys.types tp ON tp.user_type_id = c.user_type_id
    WHERE t.name = @Table1
) t1
FULL OUTER JOIN (
    SELECT c.*, tp.name DataType
    FROM DB2.sys.tables t
    JOIN DB2.sys.columns c ON c.object_id = t.object_id
    JOIN DB2.sys.types tp ON tp.user_type_id = c.user_type_id
    WHERE t.name = @Table2
) t2 ON t2.name = t1.name;
user_148689970
Related