December 9, 2008 at 4:50 pm
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.
December 9, 2008 at 7:44 pm
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.
December 9, 2008 at 8:15 pm
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]
December 10, 2008 at 6:51 am
Thank You so much for your reply..
December 10, 2008 at 6:53 am
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
Consultant - Straight Path Solutions
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
December 10, 2008 at 10:42 am
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]
December 10, 2008 at 12:06 pm
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);
December 10, 2008 at 1:18 pm
Thank You So much for your help all.
April 6, 2010 at 10:17 pm
Is it related to trigger?
April 8, 2010 at 3:37 am
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
April 8, 2010 at 2:35 pm
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.
April 8, 2010 at 2:59 pm
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
James Horsley
Workflow Consulting Limited
April 8, 2010 at 3:03 pm
August 2, 2012 at 4:43 am
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