bigator

MSSQL: Find total records of each Table in given Database

hi guys,
i wanted to do a quick post on how you can find total number of records in each table of the database.

here is the script you can use:

USE <Your Database Name>
GO

;WITH CTE_DATABASETABLESIZE (DATABASENAME, SCHEMANAME, TABLENAME, TOTALROWS)
AS
(
SELECT
 DB_NAME(), SCHEMA_NAME(SO.UID), SO.NAME, SI.ROWS
FROM
 SYSOBJECTS SO
 INNER JOIN SYSINDEXES SI
 ON SO.ID = SI.ID
WHERE
 TYPE = ‘U’ AND SI.INDID IN (0,1)
)

SELECT * FROM CTE_DATABASETABLESIZE
/*ORDER BY SCHEMANAME
    ORDER BY TOTALROWS ASC*/
ORDER BY TOTALROWS DESC

Note: Customize this query according to your needs :)

Thanks, Khilit

bigatorJanuary 23, 2013 · bigatorKhilitchandra Prajapati · bigatorOne Comment
bigatorTags: ,  Â· Posted in: Business Intelligence, General, Interview QA, SQL Server, SQL Server 2012, SQLServerPedia, SSAS, SSIS, SSRS

One Response

  1. rk - January 24, 2013

    very useful,thank you. is it specific to only certain version of sql server ?

Leave a Reply


bigator