Compare Table structure in two databases in sql server 2005

  • Hi,

    I have several databases which are having same tables and table structures, but recently we have made several changes to one main database tables. Now I want to compare the tables from main database to the other databases table and find out the differences so that I can implement the differences into all the tables in different databases.

    There are too many tables in main and other databases to do this manually.

    I can go to individual table and generate CREATE table query and compare with table in other database but it will too cumbersome to do for all those tables.

    Is there a script or Query to find the table structure differences or query to find the table structure for all the tables in a database. Basically I want the CREATE table query for all the tables in a database so that I can compare this with other databases and find the difference.

    Any help would be highly appreciated.

  • Suggest you read this SSC article

    http://www.sqlservercentral.com/articles/Product+Reviews/sqlcomparereview/272/

    And then go to the REDGATE software web site and do some reading.

    http://www.red-gate.com/products/SQL_Compare/index.htm

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Here's a start:

    Print 'changed in New:'

    Select * from MyTests.INFORMATION_SCHEMA.COLUMNS

    EXCEPT

    Select * from Util.INFORMATION_SCHEMA.COLUMNS

    Print 'changed from Old :'

    Select * from Util.INFORMATION_SCHEMA.COLUMNS

    EXCEPT

    Select * from MyTests.INFORMATION_SCHEMA.COLUMNS

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thank You so much for your reply..

  • I agree with bitbucket. Using one of the tools our there is better than trying to write code yourself. What Barry suggests will work too, and I've modified a script I found in the scripts section of SSC which works, but not as well as RedGate SQLCompare or ApexSQL's SQLDiff.

  • Agreed. What I posted is not anything like a complete solution. However, if you just want a quick way to check for column changes, this is a free way to do it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • These are what I use: the first script is for tables, the second one - for indexes:

    WITH old_columns AS(SELECT t.name as table_name,

    t.max_column_id_used,

    c.name as column_name,

    c.column_id,

    c.system_type_id,

    c.max_length

    FROM OLD_DB.sys.columns c

    JOIN OLD_DB.sys.tables t

    ON t.object_id = c.object_id

    WHERE t.type_desc = 'USER_TABLE'

    AND t.type = 'U'),

    new_columns AS(SELECT t.name as table_name,

    t.max_column_id_used,

    c.name as column_name,

    c.column_id,

    c.system_type_id,

    c.max_length

    FROM NEW_DB.sys.columns c

    JOIN NEW_DB.sys.tables t

    ON t.object_id = c.object_id

    WHERE t.type_desc = 'USER_TABLE'

    AND t.type = 'U')

    SELECT *

    FROM new_columns g

    WHERE NOT EXISTS (SELECT 1

    FROM old_columns b

    WHERE b.table_name = g.table_name

    AND b.column_name= g.column_name);

    WITH old_indexes AS (

    SELECT tab.name AS table_name,

    idx.name AS index_name,

    idx.index_id,

    idx.type AS index_type,

    idx.type_desc AS index_type_desc,

    idx.is_unique,

    idx.is_unique_constraint,

    idx.fill_factor,

    idx.allow_row_locks,

    idx.allow_page_locks

    FROM OLD_DB.sys.indexes idx

    JOIN OLD_DB.sys.tables tab

    ON tab.object_id = idx.object_id

    WHERE tab.type_desc = 'USER_TABLE'

    AND tab.type = 'U'

    AND idx.name IS NOT NULL),

    new_indexes AS (

    SELECT tab.name AS table_name,

    idx.name AS index_name,

    idx.index_id,

    idx.type AS index_type,

    idx.type_desc AS index_type_desc,

    idx.is_unique,

    idx.is_unique_constraint,

    idx.fill_factor,

    idx.allow_row_locks,

    idx.allow_page_locks

    FROM NEW_DB.sys.indexes idx

    JOIN NEW_DB.sys.tables tab

    ON tab.object_id = idx.object_id

    WHERE tab.type_desc = 'USER_TABLE'

    AND tab.type = 'U'

    AND idx.name IS NOT NULL)

    SELECT mbs.*

    FROM new_indexes mbs

    WHERE NOT EXISTS (SELECT 1

    FROM old_indexes el

    WHERE el.table_name = mbs.table_name

    AND el.index_name = mbs.index_name

    AND el.index_type = mbs.index_type

    AND el.index_type_desc = mbs.index_type_desc

    AND el.is_unique = mbs.is_unique

    AND el.is_unique_constraint = mbs.is_unique_constraint

    AND el.fill_factor = mbs.fill_factor);

  • Thank You So much for your help all.

  • Is it related to trigger?

  • Redgate SQL compare has a 14 day free trial - I recommend you try it on your current specific need and then if you are sold on the utility of the software buy it.

  • Thanks James,

    But I wanted to compare table structure from two databases using SQL server code.

    I am working on Archiving script for SQL Server 2005. Before I start archiving I wanted to compare the table structure for Live & Archive database through code.

    Rajesh.

    http://GrabAllOneDayDeal.co.nz

  • Ah that makes sense - as it is to archive you probably only need to worry about:

    1. New tables (as if tables are deleted they would still need to be in the archive for the old archived data)

    2. New columns (as again the values in the old columns may be relevant in the archive)

    3. Column type change

    I presume views, sprocs, triggers etc. are of little interest

    Best bet is probably the information_schema views as they make this quite easy - start with e.g.:

    -- Missing tables

    SELECT

    source.*

    FROM

    axxia01.INFORMATION_SCHEMA.TABLES source

    LEFT JOIN paxxia01.INFORMATION_SCHEMA.TABLES archive ON

    source.TABLE_NAME=archive.TABLE_NAME

    AND source.TABLE_SCHEMA=archive.TABLE_SCHEMA

    AND source.TABLE_TYPE=archive.TABLE_TYPE

    WHERE

    source.TABLE_TYPE = 'BASE TABLE'

    AND archive.TABLE_NAME Is Null

    do likewise for missing columns by joining the INFORMATION_SCHEMA.TABLES to INFORMATION_SCHEMA.COLUMNS (so you can get just cols for tables in both (as if whole table missing first bit covers) and so you can exclude columns from views

    Then final bit would be to do a match on table + column names from INFORMATION_SCHEMA.COLUMNS where the types are different for changed columns

  • Thanks James..

    I will give it a go..

    Rajesh

    http://GrabAllOneDayDeal.co.nz

  • @RBarryYoung

    Thank U for ur help. It worked.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply