37

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?

Community
  • 1
  • 1
chinna_82
  • 6,353
  • 17
  • 79
  • 134
  • What will you do if there are deletes as well? What if it happens on the same table (insert and a delete)? Maybe create a table trigger on your tables and an additional *audit* table that would store information for you. – Robert Koritnik Oct 18 '12 at 08:54
  • 1
    Possible duplicate: http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database – Bacon Bits Jun 02 '14 at 12:53

4 Answers4

74
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:

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • 1
    It doesn't show the correct count. If u run this statement "sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) + CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'" u will get the different result – Michael Sync Mar 12 '14 at 08:34
4

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'
slavoo
  • 5,798
  • 64
  • 37
  • 39
kavitha Reddy
  • 3,303
  • 24
  • 14
0

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 > 0
GROUP BY DbName, SchemaName, TableName
ORDER BY DbName, SchemaName, TableName
Community
  • 1
  • 1
OldCurmudgeon
  • 64,482
  • 16
  • 119
  • 213
  • I ran your code which seemed to work well so I prematurely upvoted it. Upon reviewing the final results, the RowsCount values seemed off. I added a SUM(1), MIN(RowsCount) to your final SELECT command and found that when the SUM(1) > 1, the SUM(RowsCount) was actually MIN(RowsCount)*SUM(1). (i.e.: if a table has MIN(RowsCount) = 15 & SUM(1) = 5, your query says the SUM(RowsCount) = 75 ... which is not correct). Thoughts? – programmer43229 Oct 06 '14 at 12:55
  • @programmer43229: The row count you see in the table sys.partitions is documented as "approximate". – Mike Sherrill 'Cat Recall' Nov 04 '16 at 14:59
0

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 < 2 ORDER  BY I.rows DESC
Muhammad Sohail
  • 828
  • 9
  • 18