October 18, 2012 at 12:05 pm
Hello Everyone,
I need to check if all the tables in a database are same(have same columns and datatypes) to all the tables in another database. and these two databases are in different servers..please suggest....
October 18, 2012 at 12:12 pm
gowri.4ever (10/18/2012)
Hello Everyone,I need to check if all the tables in a database are same(have same columns and datatypes) to all the tables in another database. and these two databases are in different servers..please suggest....
Check out sys.objects and sys.columns.
Something like:
select *
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
where so.name = 'YourTableNameHere'
order by column_id
If you have to compare a lot of tables you might take a look at Redgate's SQL Compare.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2012 at 12:19 pm
Hi,
There are too many tools for that. If you need it for some days just download a trial and compare databases you need. It is more visible and precise using tools.
e.g. http://www.adeptsql.com/ or
http://www.red-gate.com/products/sql-development/sql-compare/
or ...many others; just search google.
IgorMi
Igor Micev,My blog: www.igormicev.com
October 18, 2012 at 12:26 pm
hi,
I cannot use the redgate and all...what i was asked to do is to copy all the tables in a database into a temp table and then do the same thing with the other database and then compare these two tables
October 18, 2012 at 12:32 pm
gowri.4ever (10/18/2012)
hi,I cannot use the redgate and all...what i was asked to do is to copy all the tables in a database into a temp table and then do the same thing with the other database and then compare these two tables
What exactly do you mean by compare them? If you are just examining the structure then the query I posted will get you the information needed for that. If you instead need to compare actual data is becomes a bit more complicated.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2012 at 12:36 pm
I want to check if they have same column names and if their data type is same
October 18, 2012 at 12:38 pm
gowri.4ever (10/18/2012)
I want to check if they have same column names and if their data type is same
So run the query I posted. It will show you that information and then some.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2012 at 1:45 pm
with the script you provided I need to check for every table in the database..and there are more than 100 tables....it will be a very complicated and time taking...could you please tell me any other way to do it
October 18, 2012 at 2:28 pm
gowri.4ever (10/18/2012)
with the script you provided I need to check for every table in the database..and there are more than 100 tables....it will be a very complicated and time taking...could you please tell me any other way to do it
As I said previously if you need to compare more than a few tables a third party tool is by far the easiest. Without using a third party tool this is going to kind of painful and tedious.
You could possibly use something like the following query which will get all tables and the columns in that table. This is just something I tossed together quickly and is mostly untested. Maybe you can stick this in a temp table, then do the same for the other database. Then join those two tables together and find any column that doesn't match on every single column. That is a trimmed down version of what most of the 3rd party tools do. This kind of thing is not super simple which is why those tools exist.
select so.name as TableName, sc.name as ColumnName, t.name as DataType,
--need to divide by two when nchar or nvarchar because the storage space is 2 bytes per character instead of 1
case when t.name in ('nvarchar', 'nchar') then sc.max_length / 2 else sc.max_length end as MaxLength,
sc.precision, sc.scale, sc.is_nullable, sc.is_identity, sc.*
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
join sys.types t on t.system_type_id = sc.system_type_id and t.user_type_id = sc.user_type_id
where so.type = 'U'
order by so.name, column_id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2012 at 2:50 pm
What about using the query that Sean gave you for each database using and except between them?
select so.name as TableName, sc.name as ColumnName, t.name as DataType,
--need to divide by two when nchar or nvarchar because the storage space is 2 bytes per character instead of 1
case when t.name in ('nvarchar', 'nchar') then sc.max_length / 2 else sc.max_length end as MaxLength,
sc.precision, sc.scale, sc.is_nullable, sc.is_identity
from DB1.sys.objects so
join DB1.sys.columns sc on so.object_id = sc.object_id
join DB1.sys.types t on t.system_type_id = sc.system_type_id and t.user_type_id = sc.user_type_id
where so.type = 'U'
EXCEPT
select so.name as TableName, sc.name as ColumnName, t.name as DataType,
--need to divide by two when nchar or nvarchar because the storage space is 2 bytes per character instead of 1
case when t.name in ('nvarchar', 'nchar') then sc.max_length / 2 else sc.max_length end as MaxLength,
sc.precision, sc.scale, sc.is_nullable, sc.is_identity
from DB2.sys.objects so
join DB2.sys.columns sc on so.object_id = sc.object_id
join DB2.sys.types t on t.system_type_id = sc.system_type_id and t.user_type_id = sc.user_type_id
where so.type = 'U'
You should do it both ways, but it can help you.
By the way, checking "manually" would have been faster than searching the internet 😛
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply