June 15, 2011 at 11:57 am
I have a project where I need to migrate all objects to a new database (splitting from 1 file to 8 files)...during an upgrade from sql 2000 to sql 2008.
I have the new database created, all the objects created...
I have loaded the new tables with select insert statements...and am working on perfecting the script to do the work...
After I got done testing I was hoping to pull row counts for each table to compare and fix any issues.
I have an old script that does a sp_spaceused for each table but that runs for ever and never completes (would assume it would eventually).
Looking around online I have found various scripts that use DMV's (for instance)
SELECTb.name as [SchemaName],
a.name as [TableName],
SUM(d.row_count) as [RowCount]
FROMsys.tables a
inner join sys.schemas b on a.schema_id = b.schema_id
inner join sys.indexes c on a.object_id = c.object_id
inner join sys.dm_db_partition_stats d on a.object_id = d.object_id and
c.index_id = d.index_id
WHERE c.index_id < 2 and
a.name <> 'dtproperties'
GROUP BY b.name, a.name
ORDER BY b.name, a.name'
I put the results from each database into two separate tables...and did a compare...
out of 23,000 tables I got like 160 that came up as not matching...
upon review the row counts did indeed match doing spot checks on select COUNT(*) from xxx or sp_spaceused xxx
I can't figure out why they are showing inaccurate and would appreciate a bullet proof method if any exists.
Thanks in advance,
Lee
June 15, 2011 at 12:03 pm
I have used this query (taken from Dave Pinal http://blog.sqlauthority.com/2009/01/13/sql-server-find-row-count-in-table-find-largest-table-in-database-t-sql/ ) to get a rowcount for every table in the database.
USE <databasename>
GO
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO
June 22, 2011 at 9:18 am
Thanks for posting the query...I used it in a script to compare the two databases...
I am attaching my script for reference...it seems to do the job.
Thanks again,
Lee
June 22, 2011 at 12:33 pm
Cool, I looked at the script. I like it and may use it.
Does that INSERT statement really work, without using the 'VALUES' clause, ie. INSERT INTO <tablename> <return set from select statement>?
June 22, 2011 at 1:04 pm
I looked up the answer to my own question here: http://www.sommarskog.se/share_data.html#inlineUDF
Coolness. Thanks for sharing your script. One thing you'll need to do is make sure that the column names in the select statements are exactly the way they are in the create table statements, or else it won't run. I just checked :-).
I changed select statement to:
SELECT@sql_cmd_dest_db=
'SELECTb.name as [Schema_name],
a.name as [Table_Name],
SUM(d.row_count) as [Row_Count],
''DESTINATION''as [database_identifier]
June 22, 2011 at 11:04 pm
you can also use the following query to get the count of records in the tables:
SELECT OBJECT_NAME(OBJECT_ID), Rows FROM sys.partitions
WHERE INDEX_ID IN (0,1) -- 0 = Heap;1 = Clustered
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply