Below SQL generates the SELECT Script to get the record count of each table in a database.
This type of script is very useful to prepare a report after database refreshment and data migration
SELECT 'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
+ LEFT(o.name, 128) + ''' as DatabaseName, count(*) as RecordCount From ' + o.name
+ ';' AS ' Script generator to know the record count of each table in current database'
FROM sys.objects o
WHERE o.[type] = 'U'
ORDER BY o.name;