I have the following 2 Tables in my database:
Now select Count from these tables:
Now by using the following SQL Statement you can find All Tables with their rows count:
- SELECT TableName = o.name,
- Rows=max(i.rows)
- FROMsysobjects o
- INNERJOINsysindexes i
- ON o.id = i.id
- WHERE xtype ='u'
- ANDOBJECTPROPERTY(o.id,N'IsUserTable')= 1
- GROUPBY o.name
- ORDERBYRowsDESC
- GO
We can use the following SQL Statement also to get this type of result:
- DECLARE @QueryString NVARCHAR(MAX);
- SELECT @QueryString =COALESCE(@QueryString +' UNION ALL ','')
- +'SELECT '
- +''''+QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
- +'.'+QUOTENAME(sOBJ.name)+''''+' AS [TableName]
- , COUNT(*) AS [RowCount] FROM '
- +QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
- +'.'+QUOTENAME(sOBJ.name)+' WITH (NOLOCK) '
- FROMsys.objectsAS sOBJ
- WHERE
- sOBJ.type='U'
- AND sOBJ.is_ms_shipped = 0x0
- ORDERBYSCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
- EXECsp_executesql@QueryString
- GO