Compare Table structure in two databases in sql server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Compare Table structure in two databases in...

14 posts, Page 1 of 2

1 2 »»

Compare Table structure in two databases in sql server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

Mpatel-962775

Posted Tuesday, December 09, 2008 4:50 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, January 14, 2013 9:23 AM
Points: 41, Visits: 177

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.

Post #616589

bitbucket-25253

Posted Tuesday, December 09, 2008 7:44 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 5,122, Visits: 20,362

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
Before posting a performance problem please read

Post #616616

RBarryYoung

Posted Tuesday, December 09, 2008 8:15 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #616624

Mpatel-962775

Posted Wednesday, December 10, 2008 6:51 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, January 14, 2013 9:23 AM
Points: 41, Visits: 177

Thank You so much for your reply..

Post #616953

Jack Corbett

Posted Wednesday, December 10, 2008 6:53 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 10,613, Visits: 11,952

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.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #616956

RBarryYoung

Posted Wednesday, December 10, 2008 10:42 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #617237

Zeev Kazhdan

Posted Wednesday, December 10, 2008 12:06 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 4:04 AM
Points: 72, Visits: 202

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);

Post #617296

Mpatel-962775

Posted Wednesday, December 10, 2008 1:18 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, January 14, 2013 9:23 AM
Points: 41, Visits: 177

Thank You So much for your help all.

Post #617362

rajesh.shukla

Posted Tuesday, April 06, 2010 10:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 7:04 PM
Points: 7, Visits: 68

Is it related to trigger?

Post #898130

James Horsley

Posted Thursday, April 08, 2010 3:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:51 AM
Points: 142, Visits: 394

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.

James Horsley
Workflow Consulting Limited

Post #899378

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions