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

User · Accepted Answer

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

User · Answer

Try this it s simple and fast   SELECT T name AS  TABLE NAME   I rows AS  ROWCOUNT   FROM   sys tables AS T     INNER JOIN sys sysindexes AS I ON T object id   I id     AND I indid  lt  2 ORDER  BY I rows DESC

User · Answer

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

User · Answer

Posted for completeness   If you are looking for row count of all tables in all databases  which was what I was looking for  then I found this combination of this and this to work  No idea whether it is optimal or not   SET NOCOUNT ON DECLARE  AllTables table  DbName sysname SchemaName sysname  TableName sysname  RowsCount int   DECLARE       SQL nvarchar 4000  SET  SQL  SELECT       AS DbName  s name AS SchemaName  t name AS TableName  p rows AS RowsCount FROM     sys tables t INNER JOIN sys schemas s ON t schema id s schema id INNER JOIN     sys partitions p ON p OBJECT ID   t OBJECT ID   INSERT INTO  AllTables  DbName  SchemaName  TableName  RowsCount      EXEC sp msforeachdb  SQL SET NOCOUNT OFF SELECT DbName  SchemaName  TableName  SUM RowsCount   MIN RowsCount   SUM 1  FROM  AllTables WHERE RowsCount  gt  0 GROUP BY DbName  SchemaName  TableName ORDER BY DbName  SchemaName  TableName

[sql-server] MS SQL 2008 - get all table names and their row counts in a DB

Examples related to sql-server

Examples related to sql-server-2008

Examples related to row