--    query: The query field provides a simple SQL statement for a given table. 
--    schema_name:  The schema_name provides the name of the schema of the table, it is often a dbo.
--    table_name: This indicates the name of the given table.
--    row_count:  This provides the total number of rows for the given table.

--    Notes for execution: first create the temporary procedure before running. 
--    It will be deleted when closing the connection. 



create procedure #sp_table_counts 
as
begin
    declare @tables as Table
    (
        schema_name varchar(50),
        table_name varchar(255)
    )


    insert into @tables 
    select    
        schema_name ,
        table_name
    from
    (
        select name table_name, schema_name = schema_name(schema_id)
        from sys.tables
    ) [Table]


    declare @sql nvarchar(max) = '';
    declare @schema_name nvarchar(50);
    declare @table_name nvarchar(50);

    declare @results as Table
    (
        query nvarchar(max),
        schema_name nvarchar(50),
        table_name nvarchar(255),
        row_count int
    )

    declare table_cursor cursor for  
    select schema_name, table_name
    from @tables 

    open table_cursor   
    fetch next from table_cursor into @schema_name, @table_name    

    while @@fetch_STATUS = 0   
    begin     
        set @sql = 'insert into @results select ''select top 10 * from [' + 
        @schema_name + '].[' +  @table_name + '] '' query, ''' + @schema_name + ''' [schema_name], ''' + @table_name + ''
        ' [table_name], count(-1) row_count from [' + @schema_name + '].[' +  @table_name + ']; ';
        print @sql;
        execute sp_ExecuteSQL @sql;
        
        fetch next from table_cursor into @schema_name, @table_name    
    end   

    close table_cursor   
    deallocate table_cursor 

    select * from @results order by row_count desc;
end

go

exec #sp_table_counts 

drop procedure #sp_table_counts