SQL Table Rows

Posted on 2009-02-15
Last Modified: 2012-06-21
Dear All,

I want to know the number of rows of each  table in my SQL database. It will be helpful if you can give me a valuable solution for this. I need the query to get the rows of each table in my database done in SQL.

Thanks and Regards
Question by:Ikramfies
    LVL 31

    Accepted Solution

    i use

    USE DatabaseName
    CREATE TABLE #temp (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    INSERT #temp
    EXEC sp_msforeachtable sp_spaceused ?
    SELECT a.table_name,
    COUNT(*) AS col_count,
    FROM #temp a
    INNER JOIN information_schema.columns b
    ON a.table_name collate database_default
    = b.table_name collate database_default
    GROUP BY a.table_name, a.row_count, a.data_size
    ORDER BY CAST(REPLACE(a.data_size,  KB, ) AS integer) DESC
    DROP TABLE #temp

    taken from
    LVL 25

    Assisted Solution

    A quick executing one-line script would be this, but it depends on the sysindexes table which may not be up-to-date -

    SELECT OBJECT_NAME(Id), name, rowcnt from dbo.sysindexes where indid < 2 ORDER BY rowcnt DESC

    LVL 39

    Assisted Solution

    This procedure will give you an ACCURATE count of the records in each table.
    create procedure up_GetAllTableCountsInDB
    create table #TableCounts (TableName nvarchar(255), TableCount int)
    insert into #TableCounts
    exec sp_msforeachtable 'select ''?'',count(*) from ?'
    select * from #TableCounts
    drop table #TableCounts

    Open in new window


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now