locked
How to extract all the Table count from SQL database? RRS feed

  • Question

  • Dear community,

    We are using the SQL Server 2014 and we like to extract all the table count from our database.

    Database Instance : JDESQL

    Note : JDESQL has 11 Database ( JDE910, JDE_DV910,JDE_PY910 ...etc )

    We tried to find the information using the below query but this query is not listing the record count and we need to execute this query in each database to get the table information.

    SELECT * FROM INFORMATION_SCHEMA.TABLES;

    Is there a easy way to get this information from all 11 database? 

    Best Regards
    JDEE1Tips
    Systemic Software Solution


    • Edited by JDEE1Tips Monday, October 10, 2016 3:09 AM
    Monday, October 10, 2016 3:08 AM

Answers

All replies

  • Hello,

    You can use this script to get record count of all tables in a database in an effective way: Detailed list of all tables and their size

    But you have to run it for each database.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Monday, October 10, 2016 4:40 AM
  • Hi JDEEP1tips,

    This may help you.

    sp_msforeachDB 'IF ''?'' NOT IN (''master'',''tempdb'', ''model'',''msdb'') BEGIN USE [?];select ''[?]'';select substring(o.name, 1, 50) as Table_Name
    ,i.rows as Number_of_Rows 
    from sysobjects as o inner join sysindexes as i on o.id = i.id 
    where i.indid < 2 and o.xtype like ''U''
    order by o.name;end'

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/286e727f-e076-495d-9fdd-80c515cade05/how-to-get-the-row-count-of-all-tables-from-all-databases-of-a-sql-server?forum=transactsql


    Please click Mark As Answer if my post helped.

    Monday, October 10, 2016 4:41 AM
  • Here you go

    SELECT
    t.name,
    [RowCount] = SUM
    (
    CASE
    WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
    ELSE 0
    END
    )
    FROM
    sys.tables t
    INNER JOIN sys.partitions p
    ON t.object_id = p.object_id
    INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
    GROUP BY
    t.name
    ORDER BY [RowCount] DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 10, 2016 4:48 AM
  • Thanks you so much !

    But we have to run it for each database correct ?

    Best Regards
    JDEE1Tips
    Systemic Software Solution

    Monday, October 10, 2016 6:31 AM
  • Yes, otherwise you need a cursor

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 10, 2016 6:42 AM
  • Thanks for your answer.

    Disk usage report by table is more helpful !

    Best Regards
    JDEE1Tips
    Systemic Software Solution

    • Marked as answer by JDEE1Tips Monday, October 10, 2016 6:55 AM
    Monday, October 10, 2016 6:55 AM