Rework query to include the Schema in the name

Question

I am leeching off this post: Query to list number of records in each table in a database

With this procedure:

CREATE PROCEDURE ListTableRowCounts 
AS 
BEGIN 
    SET NOCOUNT ON 

    CREATE TABLE #TableCounts
    ( 
        TableName VARCHAR(500), 
        CountOf INT 
    ) 

    INSERT #TableCounts
        EXEC sp_msForEachTable 
            'SELECT PARSENAME(''?'', 1), 
            COUNT(*) FROM ? WITH (NOLOCK)' 

    SELECT TableName , CountOf 
        FROM #TableCounts
        ORDER BY TableName 

    DROP TABLE #TableCounts
END
GO

The procedure works well enough but I need it to output the name as Schema.Name and sort by that.

Is that possible? I'm not sure how to change this but you can see what it is doing below:

enter image description here

I have several instances were the table names are the same from different schemas.

score 2 · Accepted Answer · answered Feb 26 '15 at 16:59

CREATE PROCEDURE ListTableRowCounts 
AS 
BEGIN 
    SET NOCOUNT ON 

    CREATE TABLE #TableCounts
    (   SchemaName VARCHAR(500),
        TableName VARCHAR(500), 
        CountOf INT 
    ) 

    INSERT #TableCounts
        EXEC sp_msForEachTable 
            'SELECT PARSENAME(''?'', 2), PARSENAME(''?'', 1), 
            COUNT(*) FROM ? WITH (NOLOCK)' 

    SELECT SchemaName, TableName , CountOf 
        FROM #TableCounts
        ORDER BY TableName, SchemaName 

    DROP TABLE #TableCounts
END
GO

score 0 · Answer 2 · edited May 23 '17 at 12:28

Taking some code from: https://stackoverflow.com/a/1443723/4584335

and from: How do I list all tables in all databases in SQL Server in a single result set?

Could I suggest this one (just in case "sp_msForEachTable" doesn't exist anymore):

declare @sql nvarchar(max);

select @sql = isnull(@sql + N'union all ', '')
 + N'
select  b.name as "DB"
        ,a.name collate Latin1_General_CI_AI
        ,a.object_id
        ,a.schema_id
        ,' + cast(database_id as nvarchar(10)) + N'
        ,p.[Rows]
from    ' + quotename(name) + N'.sys.tables a
join      
        ' + quotename(name) + N'.sys.indexes i
    on  a.OBJECT_ID = i.object_id
    and i.index_id <= 1
join 
        ' + quotename(name) + N'.sys.partitions p
    on  i.object_id = p.OBJECT_ID
    and i.index_id = p.index_id
join    sys.databases b
    on  database_id=' + cast(database_id as nvarchar(10)) + ' '
from    sys.databases
where   state = 0
and     user_access = 0;

exec sp_executesql @sql;

Rework query to include the Schema in the name

2 Answers2