Compare structure in SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare structure in SQL Server 2005

Author

Topic

dba_raf
Starting Member

2 Posts

Posted - 2010-02-02 : 09:18:10

Hi,
I've 2 databases in SQL Server 2005: "db_old" and "db_new"
I'd like to compare structure of the databases and particularly to update the structure of db_old to db_new.
I would like to compare: tables, views, columns, sequences, triggers, etc.

In Oracle, for compare my databases I used the DBLINK with following query:

--tables present in new and not in old:
select TABLE_NAME
from user_tables
minus
select TABLE_NAME
from user_tables@NEW_TO_OLD
order by 1;

--views present in new and not in old:
select view_NAME
from user_views
minus
select view_NAME
from user_views@NEW_TO_OLD
order by 1;

--columns present in new and not in old:
SELECT TABLE_NAME, COLUMN_NAME
FROM USER_TAB_COLUMNS
MINUS
SELECT TABLE_NAME, COLUMN_NAME
FROM USER_TAB_COLUMNS@NEW_TO_OLD
ORDER BY 1;

--sequences present in new and not in old:
select sequence_name
from user_sequences
minus
select sequence_name
from user_sequences@NEW_TO_OLD;

--PK present in new and not in old:
select table_name, constraint_name
from user_constraints
where constraint_name
in (select constraint_name
from user_constraints
minus
select constraint_name
from user_constraints@NEW_TO_OLD
)
and constraint_type='P'
and constraint_name not like 'SYS%'
order by 1;

How can I generate scripts like for sql server?

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 09:31:49

http://www.sql-server-performance.com/articles/dba/database_comparison_sp_p1.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources