I am using SQL SERVER 2008 R2, I have two same DB,
How can I compare #1 & #2 for their Schema and Tables, do we have any software for it or we can do it in the SSMS itself.
You can write a sproc which will do this for you.
1.Lets get all the tables from ABC into a table variable or temp table.
2.Loop through the temp or table variable and get each table at a time.
3.Get all the column names,datatypes etc for this table and similarly get the same details for the same table from ABC1 database.
4.Also create one final result table where you just update the status against each table as match or nomatch
5.Repeat this for all the tables in the table variable.
You can use below software for compare database schema :
Redgate SQL Compare was more user friendly than other.
You can also try ApexSQL Diff - a SQL Server database comparison and synchronization tool which detects differences between database objects. It generates comprehensive reports on the found differences and can automate the synchronization process between live and versioned databases, backups, snapshots and script folders
Video - Introduction to ApexSQL Diff
Disclaimer: I work for ApexSQL as a Support Engineer
