Row count for tables in a specific schema

Question

How to get a sql query which will get me all the tables with their respect row counts within one schema

Answer 1

Below is the Microsoft SQL Server query to retrieve all Tables what the schema is for the table and the amount of rows.

to use for specific schema simply uncomment the last row of there where clause(remove /**/). and enter the schema names that you are looking for in the brackets on the last row of the where clause you can enter more then 2 just simply follow the pattern 'SchemaName','SchemaName','SchemaName'.

 SELECT 
        t.NAME AS TableName,
        s.name AS SchemaName,
        p.[Rows]
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN
        sys.schemas s on s.schema_id = t.schema_id
    WHERE 
        t.NAME NOT LIKE 'dt%' AND
        i.OBJECT_ID > 255 AND   
        i.index_id <= 1 /* AND
        s.name in ('Schemaname1','Schemaname2') */
    GROUP BY 
        t.NAME, i.object_id, i.index_id, i.name,s.name, p.[Rows]
    ORDER BY 
        object_name(i.object_id) 

if you would like to query like this SELECT owner, table_name, NUM_ROWS FROM ALL_TABLES where owner like 'Schema_name'

I would suggest creating a view like so.

CREATE VIEW ALL_TABLES
AS
      SELECT 
            t.NAME AS table_name,
            s.name AS owner,
            p.[Rows] as NUM_ROWS
        FROM 
            sys.tables t
        INNER JOIN      
            sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN 
            sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN
            sys.schemas s on s.schema_id = t.schema_id
        WHERE 
            t.NAME NOT LIKE 'dt%' AND
            i.OBJECT_ID > 255 AND   
            i.index_id <= 1 /* AND
            s.name in ('Schemaname1','Schemaname2') */
        GROUP BY 
            t.NAME, i.object_id, i.index_id, i.name,s.name, p.[Rows]
        ORDER BY 
            object_name(i.object_id) 

and then if you run the following query it will work

SELECT owner, table_name, NUM_ROWS FROM ALL_TABLES where owner like 'Schema_name'

If this helps please mark the answer as correct