compare table rows in 2 databases 1

[4650]

Hello all!

I am changing the collation on the database and doing a DTS package to copy all of the tables from one database to another. Can someone help with the script that can then (once DTS is done) compare row counts between the new and old database? Maybe just show the diferent tables or at least have that option. We have 5000 tables and manual comparison is not practical at this time as we are at the time crunch.

Thanks, George

 

[lameid]

Select 'Select Count(*), ''' + Name + ' UserTable From ' + Name'
From Sysobjects
where type = 'u'


Run the above in both databases. Then copy the results and run both scripts. Compare the results. I am sure there is a better way to go about comparing the results but I think this is a good start.

 

[lameid]

Whoa... came back because I thought I forgot the quotes around the column for name but it was worse...

Select 'Select Count(*), ''' + Name + ''' UserTable From ' + Name
From Sysobjects
where type = 'u'


Sorry about the bad example.

 

[SQLDenis]

another way

sp_msforeachtable @command1 ='select ''?'' as tableName,count(*)as Totalcount from ?'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[4650]

Great but how can I extract all table names and populate the script? othervise I would need to type in the same comand 5000 times. Please help

 

[4650]

Also,

I am getting this error:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_msforeachtable'.

 

[4650]

and error for the first command:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Sysobjects'.

Am I doing something wrong here?

 

[manmaria]

try this

sp_MSforeachtable @command1 ='select ''?'' as tableName,count(*)as Totalcount from ?'

 

[4650]

ok. the last one worked great. now, how can I run the same script on both databases and then compare line by line on the same screen (or same output file the table rows)?

 

[lameid]

I think as long as you are the DBO for the database you should have permission to run my select statement. At least it works in SQL 2000. Perhaps there is a version issue.

At anyrate I revised a little...

Select 'Select Count(*) as RecordCount, ''' + Name + ''' UserTable From ' + Name + ' Union All '
From Sysobjects
where type = 'u'

If you copy all the results of that and delete the Union All off the last line and execute it, you will get a single result set. Copy the results run from each server and compare them. Personally, I would probably paste into Excel and import into Access and then join the two tables together on Usertable and finally compare the record counts. But that is just a oneoff quick solution. I'll have to defer to the more experienced for a complete SQL Server Solution.

 

[4650]

Nope. still fails on the sysobjects. we are running 2000 with sp3 and sp4.

 

[lameid]

All the databases I checked have select permission on the sysobjects table for the Public Role.

You might try explicitly granting the account you are using select permission to the sysobjects table.

Unfortunately my SQL server experience is somewhat limited. I bet there is some configuration option that is keeping you from using it. It is a shame because I think that gives you what you are looking for.