How to get list of tables which have rows?

Question

How can I get list of tables which have rows in my database.

Select  A.Name
    From    Sys.Tables A
            -- Inner Join with ?
    -- Or What conditions ?

Is it possible?

please guide me! Thank you.

http://stackoverflow.com/questions/12950538/ms-sql-2008-get-all-table-names-and-their-row-counts-in-a-db — sgeddes, May 17 '14 at 04:56
Thanks. The answer is in here: http://stackoverflow.com/questions/12950538/ms-sql-2008-get-all-table-names-and-their-row-counts-in-a-db — Alexandre Santos, May 17 '14 at 04:59
This may also help you http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database — Dnyanesh, May 17 '14 at 05:54

Roopesh · Accepted Answer · 2014-05-18T03:29:28.810

1

This query will help.

SELECT obj.name TableName, st.row_count
            FROM sys.dm_db_partition_stats(nolock) st
            inner join sys.objects(nolock) obj on st.object_id = obj.object_id
    WHERE index_id < 2 and st.row_count > 1 and obj.type = 'u'

Edited to show user tables only.

edited May 18 '14 at 03:29

answered May 17 '14 at 04:58

Roopesh

279
2
7

It is acceptable but showing other objects other that user tables. thank you anyway. – Meysam Tolouee May 17 '14 at 05:09

score 0 · Answer 2 · answered May 17 '14 at 04:57

0

Well you could get the list of tables from INFORMATION_SCHEMA.TABLES and then cursor through each table and exec some dynamic sql to get the COUNT(*) for each table. So yes, it is possible, but I don't know if there is a more simpler solution.

answered May 17 '14 at 04:57

CodeMonkey

629
7
16

score 0 · Answer 3 · answered May 17 '14 at 05:05

0

I hope below query help you for your problem

SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE RowCnt > 0 GROUP BY sc.name,ta.name ORDER BY SUM(pa.rows) DESC

answered May 17 '14 at 05:05

Avinash Kalola

113
7

Do not edit the code which you want to copy&paste for answers. this will help me: With CTE As ( SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc.name,ta.name ) Select * From CTE Where RowCnt > 0 – Meysam Tolouee May 17 '14 at 05:12

How to get list of tables which have rows?

3 Answers3