MS SQL 2008 - get all table names and their row counts in a DB

Question

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?

Answer 1

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.

Answer 2

to get all tables in a database:

select * from INFORMATION_SCHEMA.TABLES 

to get all columns in a database:

select * from INFORMATION_SCHEMA.columns 

to get all views in a db:

select * from INFORMATION_SCHEMA.TABLES where table_type = 'view'