Solved

Database Table  Compare - SQL SERVER

Posted on 2012-03-21
13
374 Views
Last Modified: 2012-04-05
I need to do a (Table to Table) compare in two different databases on the same server.

I need to find the differences between any given set of two tables with the same name in two different databases.   I can not use a third party tool, need a script based solution possibly from the DMVs sysobjects, syscolumns etc....
0
Comment
Question by:JGH5
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 12

Expert Comment

by:NormanMaina
ID: 37748744
You can use the tablediff utility.

http://msdn.microsoft.com/en-us/library/ms162843.aspx
0
 

Author Comment

by:JGH5
ID: 37748835
Thanks Norman, However, I need a Schema Compare not a Data Compare.  Need to see if Columns are missing or if the Data Types are different when they should all be the same.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37748871
This code will list you the columns, types, etc. in a table.

select column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
where sysobjects.name = YOUR TABLE NAME HERE'
and sysobjects.xtype='U'
ORDER BY syscolumns.name
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:NormanMaina
ID: 37748950
If you are a developer and have access to VS 2011 ,you can use it to compare and sync schemas.
http://msdn.microsoft.com/en-us/library/dd193250.aspx

Or you can use this free tool:
http://www.sqleffects.com/Articles/Product/sqlAccordInfo/aboutSqlAccordCommunityEd.html
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37751075
Use sql compare by Redgate. A very helfpul tool.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37752794
Does that give you what you need, or would you prefer an actual script, as orig stated?
0
 

Author Comment

by:JGH5
ID: 37811554
An actual script is preferred since I do not have access to any of the mainstream tools.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37811932
Do you need more details than in than the script I provided above? You can easily create a tablevar with two lots of fields and fill one lot with the results from one run (on one table) of this code and the other lot with the results from another run (on a different table) of this code. Then select all from the tablevar to see differences immediately
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37812202
Working on a full script now, please just give me a little time :-) .
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 37812340
Go for it Scott! I'm actually in work, so don't have the time for that :)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37813026
Please try this and let me know what adjustments are needed :-) ::


DECLARE @db1 sysname
DECLARE @table1 sysname
DECLARE @db2 sysname
DECLARE @table2 sysname

SET @db1 = '<db1_name>'          --will default to current db if NULL/''
SET @db2 = '<db2_name>'          --will default to current db if NULL/''
SET @table1 = '<table1_name>'  --will default to @table2 if NULL/''; may include schema. if needed
SET @table2 = NULL                  --will default to @table1 if NULL/''; may include schema. if needed


--**************************************************************************************************

DECLARE @sql nvarchar(max)
DECLARE @schema1 sysname
DECLARE @schema2 sysname

IF @db1 IS NULL OR @db1 = ''
    SET @db1 = DB_NAME()
IF @table1 IS NULL OR @table1 = ''
    SET @table1 = @table2
IF @db2 IS NULL OR @db2 = ''
   SET @db2 = DB_NAME()
IF @table2 IS NULL OR @table2 = ''
    SET @table2 = @table1
   
SET @schema1 = PARSENAME(@table1, 2)
SET @table1 = PARSENAME(@table1, 1)
IF @schema1 IS NULL
    SET @schema1 = 'dbo'    
SET @schema2 = PARSENAME(@table2, 2)
SET @table2 = PARSENAME(@table2, 1)
IF @schema2 IS NULL
    SET @schema2 = 'dbo'
   
--SELECT @db1, @db2, @table1, @table2, @schema1, @schema2

SET @sql = '
SELECT
    ColumnName,
    CASE WHEN [Datatype1] <> [Datatype2] THEN ''Yes'' ELSE ''No'' END AS [Datatype Diff?],
    [$db1$?], [$db2$?],
    [Datatype1], [Datatype2],
    [Nullable?], [Identity?], [Computed?]
FROM (
SELECT
    COALESCE(c1.name, c2.name) AS ColumnName,    
    CASE WHEN c1.name IS NULL THEN ''No'' ELSE ''Yes'' END AS [$db1$?],
    CASE WHEN c2.name IS NULL THEN ''No'' ELSE ''Yes'' END AS [$db2$?],
    COALESCE(t1.name + CASE
        WHEN t1.name LIKE ''%bin%'' OR t1.name LIKE ''%char%''  THEN ''('' +
            CASE WHEN t1.max_length = -1 THEN ''MAX'' ELSE CAST(t1.max_length AS varchar(10)) END + '')''
        WHEN t1.name IN (''decimal'', ''numeric'')  THEN ''('' +
            CAST(t1.precision AS varchar(10)) + '','' + CAST(t1.scale AS varchar(10)) + '')''
        WHEN t1.name IN (''float'', ''real'')  THEN ''('' +
            CAST(t1.precision AS varchar(10)) + '')''
        ELSE ''''
        END, ''-'') AS [Datatype1],
    COALESCE(t2.name + CASE
        WHEN t2.name LIKE ''%bin%'' OR t2.name LIKE ''%char%''  THEN ''('' +
            CASE WHEN t2.max_length = -1 THEN ''MAX'' ELSE CAST(t2.max_length AS varchar(10)) END + '')''
        WHEN t2.name IN (''decimal'', ''numeric'')  THEN ''('' +
            CAST(t2.precision AS varchar(10)) + '','' + CAST(t2.scale AS varchar(10)) + '')''
        WHEN t2.name IN (''float'', ''real'')  THEN ''('' +
            CAST(t2.precision AS varchar(10)) + '')''
        ELSE ''''
        END, ''-'') AS [Datatype2],
    COALESCE(CAST(c1.is_nullable AS char(1)), ''-'') + '' / '' + COALESCE(CAST(c2.is_nullable AS char(1)), ''-'') AS [Nullable?],
    COALESCE(CAST(c1.is_identity AS char(1)), ''-'') + '' / '' + COALESCE(CAST(c2.is_identity AS char(1)), ''-'') AS [Identity?],
    COALESCE(CAST(c1.is_computed AS char(1)), ''-'') + '' / '' + COALESCE(CAST(c2.is_computed AS char(1)), ''-'') AS [Computed?]    
FROM [$db1$].sys.columns c1
FULL OUTER JOIN [$db2$].sys.columns c2 ON
    c2.name = c1.name
INNER JOIN [$db1$].sys.types t1 ON
    t1.system_type_id = c1.system_type_id AND
    t1.user_type_id = c1.user_type_id
INNER JOIN [$db1$].sys.types t2 ON
    t2.system_type_id = c2.system_type_id AND
    t2.user_type_id = c2.user_type_id
WHERE
    c1.object_id = (SELECT object_id FROM [$db1$].sys.objects o1 WHERE o1.name = ''$table1$'' AND o1.schema_id = (
                   (SELECT schema_id FROM [$db1$].sys.schemas s1 WHERE s1.name = ''$schema1$''))) AND
    c2.object_id = (SELECT object_id FROM [$db2$].sys.objects o2 WHERE o2.name = ''$table2$'' AND o2.schema_id = (
                   (SELECT schema_id FROM [$db2$].sys.schemas s2 WHERE s2.name = ''$schema2$'')))
) AS derived
ORDER BY
    ColumnName
'

SET @sql = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@sql,
    '$db1$', @db1),
    '$schema1$', @schema1),
    '$table1$', @table1),
    '$db2$', @db2),
    '$schema2$', @schema2),
    '$table2$', @table2)
 
--PRINT @sql

EXEC (@sql)
0
 

Author Comment

by:JGH5
ID: 37813096
Thank you Scott; I tested the script and it provides only the differences.  How could this be modified to include the differences plus the rest of the columns in the tables?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37813147
Hmm, it should list ALL columns from BOTH tables.

Hmm, wait, to be safe, change the INNER JOINs to LEFT OUTER JOINs.  Sorry about that :-) .

It just has columns that tell you if there were differences or not.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now