.Net'ers

During a recent purge/archive project I needed to compare two tables in different SQL Server databases to see if the schemas were the same. Basically, I was moving data from a production database to an archive database and if new fields were added or changed to any of the production tables, I needed to know and sync those changes to the related archive tables. Below are two stored procedures that I created initially in the master database to simplify the process and to allow the stored procedures to be executed from any database.

The first stored procedure (sp_GetSchema) simply retrieves the column names and other related data for the specified database table using the INFORMATION_SCHEMA.COLUMNS view (Phil Haack has a great article about Using INFORMATION_SCHEMA Views to bulletproof Sql Change Scripts). It uses dynamic SQL to construct the query because the database name needs to be included. The schema information from the production and archive tables are then compared using the second stored procedure (sp_CompareSchema).

CREATE PROCEDURE sp_GetSchema
(
    @database	NVARCHAR(128)
    ,@table		NVARCHAR(128)
)
AS
SET NOCOUNT ON

DECLARE @sql    NVARCHAR(MAX)

SET @sql = '
    SELECT  column_name
            ,CASE   WHEN is_nullable = ''YES''
                    THEN ''NULL''
                    ELSE ''NOT NULL''
                    END AS is_nullable
            ,CASE   WHEN CHARINDEX(data_type, ''decimal, numeric'') > 0
                        THEN UPPER(data_type)
                            + ''(''
                            + CONVERT(NVARCHAR, numeric_precision)
                            + '', ''
                            + CONVERT(NVARCHAR, numeric_scale)
                            + '')''
                    WHEN CHARINDEX(data_type, ''varchar, nvarchar,
                            varbinary'') > 0
                            AND character_maximum_length = -1
                        THEN UPPER(data_type) + ''(MAX)''
                    WHEN CHARINDEX(data_type, ''char, nchar, varchar,
                            nvarchar, varbinary'') > 0
                        THEN UPPER(data_type)
                            + ''(''
                            + CONVERT(NVARCHAR, character_maximum_length)
                            + '')''
                        ELSE UPPER(data_type)
                    END AS data_type
            ,ordinal_position
            ,table_name
            ,''' + @database + ''' as dbname
    FROM    ' + @database + '.INFORMATION_SCHEMA.COLUMNS
    WHERE  table_name = ''' + @table + ''' '

EXEC( @sql )

SET NOCOUNT OFF

Here’s an example of executing the sp_GetSchema stored procedure and the output generated.

EXEC sp_GetSchema @database='Action', @table='Order'

column_name  is_nullable data_type ordinal_position table_name dbname
------------ ----------- --------- ---------------- ---------- ------
OrderId      NOT NULL    INT       1                Order      Action
CustomerId   NOT NULL    INT       2                Order      Action
OrderDate    NOT NULL    DATETIME  3                Order      Action
RequiredDate NULL        DATETIME  4                Order      Action
ShippedDate  NULL        DATETIME  5                Order      Action
Freight      NULL        MONEY     6                Order      Action
Version      NOT NULL    TIMESTAMP 7                Order      Action

A few things to note about the sp_CompareSchema stored procedure. It uses temporary tables to house the schema information (retrieved via sp_GetSchema) for the tables to compare because you cannot insert the result from a stored procedure into a table variable and it compares the column names, data type and nullability. I am using the characters “T”, “A” and “D” to indicate a different in type, a column to be added and a column to be deleted respectively in the schema diff table. Technically, you could move the sp_GetSchema logic into the sp_CompareSchema stored procedure, but I am using it for other purposes so I kept it separate.

CREATE PROCEDURE sp_CompareSchema
(
    @database1  NVARCHAR(128)
    ,@database2 NVARCHAR(128)
    ,@table1    NVARCHAR(128)
    ,@table2    NVARCHAR(128)
)
AS

    SET NOCOUNT ON

    DECLARE @ErrorCode      INT
    DECLARE @ErrorMsg       NVARCHAR(500)
    DECLARE @alterScript    NVARCHAR(MAX)

    SET @alterScript = ''

    -----------------------------
    -- CREATE TEMP TABLES
    -----------------------------
    IF( OBJECT_ID('#SCHEMA_T1') IS NOT NULL ) DROP TABLE #SCHEMA_T1
    IF( OBJECT_ID('#SCHEMA_T2') IS NOT NULL ) DROP TABLE #SCHEMA_T2

    -- Schema for table1
    CREATE TABLE #SCHEMA_T1
    (
        column_name     NVARCHAR(128)
        ,is_nullable    NVARCHAR(64)
        ,data_type      NVARCHAR(32)
        ,position       INT
        ,table_name     NVARCHAR(128)
        ,database_name  NVARCHAR(128)
    )
    -- Schema for table2
    CREATE TABLE #SCHEMA_T2
    (
        column_name     NVARCHAR(128)
        ,is_nullable    NVARCHAR(64)
        ,data_type      NVARCHAR(32)
        ,position       INT
        ,table_name     NVARCHAR(128)
        ,database_name  NVARCHAR(128)
    )
    -- Schema difference table
    DECLARE @SCHEMA_DIFF TABLE
    (
        id              INT IDENTITY(1, 1)
        ,database_name  NVARCHAR(128)
        ,column_name    NVARCHAR(128)
        ,is_nullable    NVARCHAR(64)
        ,data_type      NVARCHAR(32)
        ,diff_type      NVARCHAR(5)
    )

    -----------------------------
    -- POPULATE TEMP TABLES
    -----------------------------
    -- Get the fields for database1.table1 (cannot insert the result from a sproc into a table variable)
    INSERT INTO #SCHEMA_T1 (column_name, is_nullable, data_type, position, table_name, database_name)
        EXEC sp_GetSchema @database=@database1, @table=@table1

    -- Get the fields for database2.table2
    INSERT INTO #SCHEMA_T2 (column_name, is_nullable, data_type, position, table_name, database_name)
        EXEC sp_GetSchema @database=@database2, @table=@table2

    -----------------------------
    -- COMPARE THE FIELDS
    -----------------------------
    -- T - indicates a difference in Data Types...ALTER column in Target
    -- A - indicates a missing Column in the Target...ADD column to Target
    -- D - indicates a missing Column in the Source...DROP column from Target
    INSERT INTO @SCHEMA_DIFF
        SELECT  s.database_name, s.column_name, s.is_nullable, s.data_type,
                CASE WHEN (s.data_type != t.data_type) THEN 'T'
                     WHEN (s.column_name IS NOT NULL AND t.column_name IS NULL) THEN 'A' ELSE '' END AS diff_type
        FROM    #SCHEMA_T1 s
                FULL OUTER JOIN #SCHEMA_T2 t on s.column_name = t.column_name
        WHERE   s.data_type != t.data_type
        OR      (s.column_name IS NOT NULL AND t.column_name IS NULL)
        UNION ALL
        SELECT  t.database_name, t.column_name, t.is_nullable, t.data_type,
                CASE WHEN (s.column_name IS NULL AND t.column_name IS NOT NULL) THEN 'D' ELSE '' END AS diff_type
        FROM    #SCHEMA_T1 s
                FULL OUTER JOIN #SCHEMA_T2 t on s.column_name = t.column_name
        WHERE   s.column_name IS NULL AND t.column_name IS NOT NULL

    SELECT * FROM @SCHEMA_DIFF

    DROP TABLE #SCHEMA_T1
    DROP TABLE #SCHEMA_T2

    SET NOCOUNT OFF

Here’s an example of executing the sp_CompareSchema stored procedure and the output generated.

Exec sp_CompareSchema
    @database1  = 'AdventureWorks'
    ,@database2 = 'AdventureWorksLT'
    ,@table1    = 'Product'
    ,@table2    = 'Product'

id  database_name     column_name             is_nullable  data_type       diff_type
------------------------------------------------------------------------------------
1   AdventureWorks    MakeFlag                NOT NULL     BIT             A
2   AdventureWorks    FinishedGoodsFlag       NOT NULL     BIT             A
3   AdventureWorks    SafetyStockLevel        NOT NULL     SMALLINT        A
4   AdventureWorks    ReorderPoint            NOT NULL     SMALLINT        A
5   AdventureWorks    SizeUnitMeasureCode     NULL         NCHAR(3)        A
6   AdventureWorks    WeightUnitMeasureCode   NULL         NCHAR(3)        A
7   AdventureWorks    DaysToManufacture       NOT NULL     INT             A
8   AdventureWorks    ProductLine             NULL         NCHAR(2)        A
9   AdventureWorks    Class                   NULL         NCHAR(2)        A
10  AdventureWorks    Style                   NULL         NCHAR(2)        A
11  AdventureWorks    ProductSubcategoryID    NULL         INT             A
12  AdventureWorksLT  ProductCategoryID       NULL         INT             D
13  AdventureWorksLT  ThumbNailPhoto          NULL         VARBINARY(MAX)  D
14  AdventureWorksLT  ThumbnailPhotoFileName  NULL         NVARCHAR(50)    D

The data from the @SCHEMA_DIFF table could be used to build an alter script that would be applied against the archive table to sync the schema changes.

Tags: Joins, Schema, SQL, TSQL

This entry was posted on Friday, October 15th, 2010 at %I:%M %p and is filed under SQL, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.