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.

CodePudding user response：

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]

CodePudding user response：

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
) c1
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
) c2 ON c2.name = c1.name;