Finding The Row Count For All Files In An iSeries Library And All Tables In An SQL Server Database

Ive 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
FROM QSYS2.SYSTABLESTAT
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:

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows
FROM sys.tables AS t
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id
AND i.indid > 2
WHERE i.rows > 0
ORDER BY i.rows DESC, t.name DESC

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:

SELECT 
    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
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc, t.Name 

Are there any methods that you use to do a quick data integrity check?

Leave a Reply