Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Compare Table structure in two databases in sql server 2005 Expand / Collapse
Author
Message
Posted Tuesday, December 09, 2008 4:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, December 09, 2008 7:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 5,473, Visits: 23,558
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
Posted Tuesday, December 09, 2008 8:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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
Posted Wednesday, December 10, 2008 6:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, December 10, 2008 6:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:17 AM
Points: 10,911, Visits: 12,554
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
Posted Wednesday, December 10, 2008 10:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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
Posted Wednesday, December 10, 2008 12:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 11:15 AM
Points: 100, Visits: 251
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
Posted Wednesday, December 10, 2008 1:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, April 06, 2010 10:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 4:50 PM
Points: 7, Visits: 70
Is it related to trigger?
Post #898130
Posted Thursday, April 08, 2010 3:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:30 AM
Points: 159, Visits: 427
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 »

Add to briefcase 12»»

Permissions Expand / Collapse