I have the following 2 Tables in my database:

Tables

Now select Count from these tables:

Tables

Now by using the following SQL Statement you can find All Tables with their rows count:

Tables

  1. SELECT TableName = o.name,
  2. Rows=max(i.rows)
  3. FROMsysobjects o
  4. INNERJOINsysindexes i
  5. ON o.id = i.id
  6. WHERE xtype ='u'
  7. ANDOBJECTPROPERTY(o.id,N'IsUserTable')= 1
  8. GROUPBY o.name
  9. ORDERBYRowsDESC
  10. GO
We can use the following SQL Statement also to get this type of result:

Tables
  1. DECLARE @QueryString NVARCHAR(MAX);
  2. SELECT @QueryString =COALESCE(@QueryString +' UNION ALL ','')
  3. +'SELECT '
  4. +''''+QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
  5. +'.'+QUOTENAME(sOBJ.name)+''''+' AS [TableName]
  6. , COUNT(*) AS [RowCount] FROM '
  7. +QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
  8. +'.'+QUOTENAME(sOBJ.name)+' WITH (NOLOCK) '
  9. FROMsys.objectsAS sOBJ
  10. WHERE
  11. sOBJ.type='U'
  12. AND sOBJ.is_ms_shipped = 0x0
  13. ORDERBYSCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
  14. EXECsp_executesql@QueryString
  15. GO