-2

I want to have 2 columns: Table Name and Row Count

When I run this, I get 12 table names in my database. How do I then count the rows in each one without having to union 12 count queries together:

SELECT T.name FROM sys.tables t WHERE name LIKE '%20170101%'
Improve this question
1

1 Answer 1

Reset to default
0

Did some more research....I guess these details are already stored in a catalog view that you can just query:

SELECT T.name TableName,i.Rows NumberOfRows FROM sys.tables T JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID WHERE indid IN (0,1) AND t.name LIKE '%20170101%' ORDER BY i.Rows DESC,T.name
Improve this answer
4
  • 1
    How does this work out when you have multiple indexes on the same table? What if all of the indexes are filtered indexes (I imagine the row count would be incorrect then)? I would also imagine if you had a heap table (a table with no indexes) that table would get filtered out of this query too. Commented Dec 27, 2021 at 21:23
  • 2
    @J.D.: I think the indid IN (0,1) filter makes sure that for each table either the heap or the clustered index is checked. AFAIK, the ID of 0 always refers to a heap and 1 to a clustered index. Commented Dec 28, 2021 at 0:25
  • 2
    sys.sysindexes should not be used since it is a compatibility view for sql server 2000. And unlike the link in the first comment, the value of i.Rows should be considered as more of an estimate since that column is not updated in real-time Commented Dec 28, 2021 at 1:01
  • This also doesn't take into account partitioned tables, you need to SUM by t.object_id Commented Dec 29, 2021 at 17:14

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.