Here’s a neat script I wrote to list every database with the total number of rows in it.
The idea was to do a quick-n-dirty post-migration checksum between the old sql2000 and new sql2012 databases – to see which ones needed updating.
--database_compare.sql
EXECUTE sp_MSforeachdb 'use ?; DBCC UPDATEUSAGE(0) WITH COUNT_ROWS;'
create table #temp (DBName varchar(100), TotalRows int)
exec sp_msforeachdb 'insert into #temp select ''?'', sum(rows)
from [?].[dbo].sysindexes i join [?].[dbo].sysobjects o on o.id=i.id
where indid < 2 and type=''U'''
select *
from #temp
where DBName not in ('master','model','msdb','tempdb')
order by 1--2
drop table #temp
Database Consultant 