SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare Table structure in two databases in sql server 2005


Compare Table structure in two databases in sql server 2005

Author
Message
Mpatel-962775
Mpatel-962775
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 178
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.
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15769 Visits: 25280
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
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34982 Visits: 9518
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."
Mpatel-962775
Mpatel-962775
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 178
Thank You so much for your reply..
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44653 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34982 Visits: 9518
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."
BOR15K
BOR15K
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 460
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);
Mpatel-962775
Mpatel-962775
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 178
Thank You So much for your help all.
rajesh.shukla
rajesh.shukla
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 73
Is it related to trigger?
James Horsley
James Horsley
Right there with Babe
Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)Right there with Babe (735 reputation)

Group: General Forum Members
Points: 735 Visits: 451
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search