Solved

How to compare Database tables / schema in SQL Server 2005 ?

Posted on 2011-02-17
4
552 Views
Last Modified: 2012-06-27
Hi All,

Is it possible to create or do it from SSMS to compare two different schema ?

I need to compare database table in my current production DB to see which one is different in order to generate delta (upgrade script)

Thanks.
0
Comment
Question by:jjoz
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34922413
What exactly you want to compare? Do you want to check the no. of tables in one schema to another schema or between two databases?
0
 
LVL 1

Author Comment

by:jjoz
ID: 34922434
'Do you want to check the no. of tables in one schema to another schema or between two databases?"
yes exactly or even better the time last accessed or used by someone.

because I'm not sure which one is safe to delete at the moment.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34922511
-- This query gives the no. of tables in each Schema. You need to run this both on Production and your dev server for same database to check the no. of tables
select TABLE_SCHEMA,COUNT(*) TableCount from INFORMATION_SCHEMA.TABLES group by TABLE_SCHEMA
-- You can get the last modified date of a table with the below query
select * from sys.tables order by modify_date desc

Open in new window

0
 
LVL 1

Author Closing Comment

by:jjoz
ID: 34931994
many thanks man !
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question