MS SQL 2008 - get all table names and their row counts in a DB
row, sql-server, sql-server-2008
Solution
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
ORDER BY SUM(pa.rows) DESC
See this:
- SQL SERVER – Find Row Count in Table – Find Largest Table in Database.
Problem
I'm trying to identify which tables are affected by a data update though an application (developed by others). There are more than 200 tables in the DB and I'd like to avoid checking each of them for changes. Is there any other way where I can list all table names along with their row count? ``` select table_name from information_schema.tables ``` List out all the tables in my DB buy how do I include number of rows as well?