I’ve been moving several EnterpriseOne databases to SQL Server lately. In order to do a quick check to make sure that all the data was transferred, I like to compare the number of rows on all the tables. Below are the SQL queries that I use to get a row count on all tables in a database and all files in a library:
iSeries – SQL query to get a row count on all files in an iSeries library:
SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS
WHERE TABLE_SCHEMA = 'PRODDTA'
AND NUMBER_ROWS > 0
ORDER BY NUMBER_ROWS DESC, TABLE_NAME DESC
NOTE: the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.
SQL Server – SQL query to get a row count on all tables in an SQL Server database:
UPDATE: The following SQL statement will not only give you the row counts of all the tables in the SQL Server database it will also give you the amount of disk space that it is taking:
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows
TotalSpaceMB desc, t.Name
Are there any methods that you use to do a quick data integrity check?