ansaurus

Question

How To find the Changes happened to the SQL Server since last Migration

Answer 1

+2 A:

try this:

SELECT
    modify_date
        ,type_desc
        ,name
    FROM sys.objects
    WHERE is_ms_shipped=0
        --AND modify_date>='yyyy/mm/dd'  <--optionally put in your date here
    ORDER BY 1 DESC

KM 2010-09-28 16:47:03

Can You please let me know what is the is_ms_shipped?

Anoop 2010-09-28 16:49:43

`is_ms_shipped`=`Object is created by an internal SQL Server component.` [sys.objects (Transact-SQL)](http://msdn.microsoft.com/en-us/library/ms190324.aspx)

KM 2010-09-28 16:50:43

There are a lot of database entities that are not in `sys.objects`: database principals, permissions, files and filegroups, schemas, services, certificates, types, assemblies and so on and so forth. I reckon though that most of the *interesting* entities are in `sys.objects`.

Remus Rusanu 2010-09-28 16:51:39

@Remus Rusanu, OP was asking about: *Stored Procedure,and Views and functions*. All of those are in sys.objects

KM 2010-09-28 16:56:02

Thanks for the detailed explanation.I will try to do more research on this.

Anoop 2010-09-28 17:59:00

Answer 2

+3 A:

Use a schema comparison tool like the VSDB Schema Compare (see Compare and Synchronize Database Schemas) or Red Gate's SQL Compare.

Remus Rusanu 2010-09-28 16:49:12

+1 because this is the right thing to do but he did say *We are not using any third Party Tools.* so I'm not sure this is the answer he is looking for.

Mark Byers 2010-09-28 16:52:40

I actually though he means no third party tools that might have modified the schema, but now I realize you're probably right.

Remus Rusanu 2010-09-28 16:54:01

Thanks for the Replies. It's been really helpful.

Anoop 2010-09-28 17:58:16

Answer 3

A:

If you have the old version and new version installed in different SQL Server, then go to the SQL Server Management Studio, right click on your database and choose "Generate Scripts" from the Tasks menu. Choose to export the objects that you are interested in.

Do it for both and diff them.

Lou Franco 2010-09-28 16:49:46

ansaurus

tags:

views:

answers:

How To find the Changes happened to the SQL Server since last Migration

related questions