DECLARE @SQLString nvarchar (255),
@ParmDefinition nvarchar (255)

DECLARE @rowsCount int
DECLARE @tablename sysname, @Empty char (1)

DECLARE FindNONEmptyTables CURSOR READ_ONLY

FOR SELECT TABLE_SCHEMA+'.'+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN FindNONEmptyTables

FETCH NEXT FROM FindNONEmptyTables INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN

SET @SQLString = N'
SELECT @rowsCount= COUNT(*) FROM ' + @tablename +
'; IF EXISTS (SELECT * FROM ' + @tablename + ') set
@Empty = ''N'' ELSE set @Empty = ''Y'''
SET @ParmDefinition = N'@tablename sysname, @Empty char(1) OUTPUT, @rowsCount int OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@tablename = @tablename,
@Empty = @Empty OUTPUT,
@rowsCount = @rowsCount OUTPUT

IF @Empty = 'N'
BEGIN
 PRINT @tablename + ' rows count is :' + CAST (@rowsCount AS NVARCHAR(50))
END
FETCH NEXT FROM FindNONEmptyTables INTO @tablename
END

CLOSE FindNONEmptyTables
DEALLOCATE FindNONEmptyTables
GO