Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Count All Tables rows in SQL Server

Article:
Viewed:  646 
Posted On:  13/03/2016 04:01:23 
How we can show table and their number of rows in SQL Server? 

 

In this article I am going to show how we can count total number of rows in every SQL Server table.

I have 2 below Tables in my Data Base:

1.png

Image 1.

Now Select Count From these tables:

2.png

Image 2.

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

3.png

Image 3.

SELECT   TableName = o.name,
         Rows = max(i.rows)
FROM     sysobjects o
         INNER JOIN sysindexes i
           ON o.id = i.id
WHERE    xtype = 'u'
         AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY Rows DESC
GO

We can use below SQL Statement also to get this type of result:

4.png

Image 4.

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) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO
 

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us