SQL record count by company

As part of our Data Upgrade from AX 2012 to Dynamics 365 Finance and Operations we had to ensure a way of telling how many records do we have per data area. Data import via entities are company-based, so we had to find out a reliable way to tell if everything has been successfully transferred. The following script can tell exactly that, show the SQL record count by company:

SET NOCOUNT ON
DECLARE @tableName NVARCHAR(255);
DECLARE @statement NVARCHAR(MAX);
 
-- Temporary table for storing record counts
CREATE TABLE #jdm_count (TableName NVARCHAR(255), Company NVARCHAR(4), RecordCount int)
 
-- Cursor for getting list of User-created tables
DECLARE cur_name CURSOR FOR
       SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
              + '.' + QUOTENAME(sOBJ.name)
       FROM sys.objects as sOBJ
       WHERE
              sOBJ.type = 'U'
              AND sOBJ.is_ms_shipped = 0x0
       ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name;
 
OPEN cur_name
 
-- Loop tables
FETCH NEXT FROM cur_name INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
       -- Construct SQL Statement for getting company-specific record count
       SELECT @statement = 'SELECT ''' + @tableName + ''' AS [TableName]'
              + IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ', DATAAREAID AS [Company]', ', '''' AS [COMPANY]')
              + ',COUNT(*) AS [RowCount] FROM ' + @tableName + ' WITH (NOLOCK)'
              + IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ' GROUP BY [DATAAREAID]', '')
              + ' HAVING COUNT(*) > 0';
              
       -- Insert statement results in temporary table
       INSERT INTO #jdm_count (TableName, Company, RecordCount)
              EXEC sp_executeSQL @statement;
 
       FETCH NEXT FROM cur_name INTO @tableName
END
 
CLOSE cur_name
DEALLOCATE cur_name
 
-- Display results
SELECT * FROM #jdm_count
ORDER BY RecordCount DESC, TableName, Company
 
DROP TABLE #jdm_count
SET NOCOUNT OFF

The post SQL record count by company appeared first on DAXRunBase.