September 19, 2013 at 6:55 am
I've been asked to compare an older copy of a DB to a current copy, to look for any schema changes. The problem is, while I suspect Red Gates SQL Compare would do this zip-zip-easy, due to the security of the environment it's a PITA to get a 3rd party tool approved.
So, is there any easy way in SSMS to compare two DBs? Everything I've found so far indicates no, seeing as I have no access to Visual Studio (which has a tool built-in, but while this is approved software it has it's own issues with getting it,) and my comment above about a 3rd party tool.
Am I going to be reduced to coding up something to list each column, its datatype, FK relationships, etc, then running it against the current and previous DB and eyeballing the list for changes?
Thanks,
Jason
September 19, 2013 at 7:06 am
I was going to suggest SQL Server Data Tools, which is Visual Studio of course...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2013 at 7:28 am
Not sure if this will give you all you require, have you tried:
Using SSMS
Right click on DB
Select Reports
Click on Schema change history
September 19, 2013 at 7:33 am
Ron, that may do some good. One question on the report, does it only go back to the last time the SQL Server service was restarted? I'm presuming yes based on the report I'm currently looking at.
Thanks,
Jason
September 19, 2013 at 8:09 am
Checked using my play database and it went back to 9/17 (2 days) and my play DB gets shut down each night.
But of course that is a very, very short duration - so I can not say for sure.
September 19, 2013 at 9:02 am
Have you tried using the view INFORMATION_SCHEMA.COLUMNS?
It might have the information you need to compare.
September 19, 2013 at 10:40 am
Am I going to be reduced to coding up something to list each column, its datatype, FK relationships, etc, then running it against the current and previous DB and eyeballing the list for changes?
Luis mentioned the information_schema.
You could get most (maybe all) of what you need by querying the tables information_schema (tables, constraints, views, stored procs, functions, etc...) Below are a couple examples.
--CHECK Constraints
(
-- stuff that's in db1 that's not in db2
SELECT --CONSTRAINT_CATALOG
Constraint_schema, constraint_name, check_clause
FROM db1.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
EXCEPT
SELECT Constraint_schema, constraint_name, check_clause
FROM db2.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
)
UNION ALL
(
-- stuff that's in db2 that's not in db1
SELECT Constraint_schema, constraint_name, check_clause
FROM db2.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
EXCEPT
SELECT Constraint_schema, constraint_name, check_clause
FROM db1.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
)
--TABLES
(
SELECT -- TABLE_CATALOG
TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM db1.INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM db2.INFORMATION_SCHEMA.TABLES
)
UNION ALL
(
SELECT table_schema, table_name, table_type
FROM db2.INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT table_schema, table_name, table_type
FROM db1.INFORMATION_SCHEMA.TABLES
)
A couple things to note:
1) Exclude the column that defines the DB name; it's usually the first one (e.g. CONSTRAINT_CATALOG or TABLE_CATALOG)
2) You will have to use the object_ID function for routine_definitions (e.g. stored procs, functions)
EDIT: Typo in my code
-- Itzik Ben-Gan 2001
September 19, 2013 at 1:09 pm
Thanks for the suggestions, and I'm still working on this. I did come up with something to give a very quick-and-dirty overview of the tables / columns / datatypes within the DBs using sys.tables and sys.all_columns.
I'm sure there are plenty of ways this could be improved, but so far for what I need it's a start...
use [ExistingDB];
select
SCHEMA_NAME(st.schema_id) as [SchemaName],
st.name as [TableName],
sac.name as [ColumnName],
(select name from sys.types where user_type_id = sac.user_type_id) as [DataType],
sac.max_length as [MaxLen],
sac.precision as [Precision]
into #currentDB
from sys.tables as st
inner join sys.all_columns as sac
on st.object_id = sac.object_id
use [RestoredDB];
select
SCHEMA_NAME(st.schema_id) as [SchemaName],
st.name as [TableName],
sac.name as [ColumnName],
(select name from sys.types where user_type_id = sac.user_type_id) as [DataType],
sac.max_length as [MaxLen],
sac.precision as [Precision]
into #OldDB
from sys.tables as st
inner join sys.all_columns as sac
on st.object_id = sac.object_id
select
CDB.schemaname as [Current Schema]
, OldDB.schemaname as [Old Schema]
, CDB.TableName as [Current Table]
, OldDB.TableName as [Old Table]
, CDB.ColumnName as [Current Column]
, OldDB.ColumnName as [Old Column]
/*, CDB.DataType as [Current Datatype]
, OldDB.DataType as [Current Datatype]
, CDB.MaxLen as [Current MaxLength]
, OldDB.MaxLen as [Current MaxLength]
, CDB.Precision as [Current Precision]
, OldDB.Precision as [Current Precision]*/
from #currentDB as CDB
full join #OldDB as OldDB
on CDB.TableName = OldDB.TableName
order by CDB.SchemaName, CDB.TableName
drop table #currentDB
drop table #OldDB
September 19, 2013 at 3:15 pm
You're on the right track; definitely use the sys. views rather than the INFORMATION_SCHEMA views, which are not reliable in SQL Server (from 2005 on).
These are the basic tables you'll need:
sys.columns
sys.foreign_key_columns
sys.index_columns
If you need to consider functions and/or stored proc parameters, you'll want to add:
sys.parameters
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 19, 2013 at 3:18 pm
You can use a FULL OUTER JOIN to find columns that are in only one object and not the other.
I can post sample code for that if you'd like.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 19, 2013 at 4:59 pm
ScottPletcher (9/19/2013)
You can use a FULL OUTER JOIN to find columns that are in only one object and not the other.I can post sample code for that if you'd like.
I did some checking, a FULL JOIN and FULL OUTER JOIN are the same in SQL2008R2 (which is what the server is.) That's why I set the code up the way I did, you can rather quickly see what tables do and don't exist in which DB, did the schema change, you can even check the datatypes by uncommenting a few lines in the last select...
I've already found a query to return a listing of FKs in a table, and it won't be any work at all to take that query and this one to generate a similar listing.
September 20, 2013 at 11:56 am
You might consider using SSMS to create a script for the database/table to a file (right click on table name and choose script as). Then use the Microsoft Word compare option to do a compare. I've used this on occasion and it seems to work out well. The Microsoft Word compare function seems to be pretty good at finding the actual differences and not get fouled up by new lines/fields.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply