MS SQL 2005 (T-SQL) Row Count for Each Table

At work I needed a solution to give me a count of how many rows each table contained. I’ve always liked phpMyAdmin’s ability to list all the tables and show their size and row count. I’ve found it immensely helpful. However, I couldn’t find anything similar for SQL Server Manager Studio 2005. I found a blog post that pointed me in the right direction on how to get the row counts, but I couldn’t get their script to run on the SQL Server 2005. Since I figured this would be a useful script to have I would re-write it and comment it for others. Here it is:

[sql]
— Select Database
— This should be the only configuration you need
USE Your_Database
GO

— Create a cursor to loop through the System Ojects and get each table name
DECLARE TBL_CURSOR CURSOR
— Declare the SQL Statement to cursor through
FOR ( SELECT Name FROM Sysobjects WHERE Type=’U’ )

— Declare the @SQL Variable which will hold our dynamic sql
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = ”;
— Declare the @TblName Variable which will hold the name of the current table
DECLARE @TblName NVARCHAR(MAX);

— Open the Cursor
OPEN TBL_CURSOR

— Setup the Fetch While that will loop through our cursor and set @TblName
FETCH NEXT FROM TBL_CURSOR INTO @TblName
— Do this while we are not at the end of the record set
WHILE (@@FETCH_STATUS <> -1)
BEGIN
— Appeand this table’s select count statement to our sql variable
SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

— Pull the next record
FETCH NEXT FROM TBL_CURSOR INTO @TblName
— End the Cursor Loop
END

— Close and Clean Up the Cursor
CLOSE TBL_CURSOR
DEALLOCATE TBL_CURSOR

— Since we were adding the UNION at the end of each part, the last query will have
— an extra UNION. Lets trim it off.
SET @SQL = LEFT(@SQL,LEN(@SQL)-6);

— Lets do an Order By. You can pick between Count and Table Name by picking which
— line to execute below.
SET @SQL = @SQL + ‘ ORDER BY Count’;
–SET @SQL = @SQL + ‘ ORDER BY Table_Name’;

— Now that our Dynamic SQL statement is ready, lets execute it.
EXEC (@SQL);
GO
[/sql]

How it works

A basic description would be it gets a listing of each table inside the the System Objects table and creates a Dynamic SQL Statement that get a count from each table and return a record set with the Table’s name and count. Pretty straight forward, and I commented just about every little thing in the script so it should be easy to follow along. Let me know if you have any suggestions or tips for making it better. Thanks!

12 thoughts on “MS SQL 2005 (T-SQL) Row Count for Each Table

  1. replaced cursor with a table vriable & a while loop. More efficient , though not noticable with small datasets such as these:
    ——-
    — Select Database
    — This should be the only configuration you need
    USE Testing
    GO
    declare @Tables table (id integer identity(1,1),TName varchar(max))
    insert into @Tables (TName) SELECT Name FROM Sysobjects WHERE Type=’U’
    — Declare the @SQL Variable which will hold our dynamic sql
    DECLARE @SQL NVARCHAR(MAX);
    Declare @counter integer
    — Declare the @TblName Variable which will hold the name of the current table
    DECLARE @TblName NVARCHAR(MAX);
    SET @SQL = ”;
    select @counter = 1
    While @counter <= (select max(id) from @Tables)
    Begin–While Loop
    select @TblName = (select TName from @Tables where id = @Counter)
    — Appeand this table’s select count statement to our sql variable
    SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;
    select @counter = @counter+1
    End–While Loop

    — Since we were adding the UNION at the end of each part, the last query will have
    — an extra UNION. Lets trim it off.
    SET @SQL = LEFT(@SQL,LEN(@SQL)-6);

    — Lets do an Order By. You can pick between Count and Table Name by picking which
    — line to execute below.
    SET @SQL = @SQL + ‘ ORDER BY Count’;
    –SET @SQL = @SQL + ‘ ORDER BY Table_Name’;

    — Now that our Dynamic SQL statement is ready, lets execute it.
    EXEC (@SQL);
    GO
    ——-

    Like

  2. try this
    use yourdatabasename

    SELECT name, rows FROM sysindexes where impid < 0 order by name

    Like

  3. The sysindexes query is fine but relies upon your database stats being up-to-date.

    Also I found that square brackets were needed around the tablename on line 26 – in the dynamic query (some of my tables begin with a numeric character)

    instead of:
    SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’

    use:
    SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM [‘+@TblName+’] ) UNION’

    Like

  4. Wouldn’t this be easier?!

    SELECT ‘select ”’+ name + ”’, count(*) from ‘ + name + ‘ union’
    FROM Sysobjects
    WHERE Type=’U’
    ORDER BY 1;

    Copy + paste the results, remove the trailing ‘union’ and add your ‘order by’ clause.

    Like

  5. Great post. Only issue I had was that my tables all use different schemas and not the default “dbo” schema, so I had to join to the sys.schemas table on schema_id and then concatenate the two columns from the sys.objects and sys.schemas tables together to get it to work.

    Quick question: Is there a way to list the row counts as well as the table sizes?

    Like

  6. Nice little post for this. I tweaked it for SQl Server 2000 and its limitation on varchar size. Left the cursor use in:

    — Create a cursor to loop through the System Ojects and get each table name
    DECLARE TBL_CURSOR CURSOR
    — Declare the SQL Statement to cursor through
    FOR (SELECT Name FROM Sysobjects WHERE Type=’U’)

    — Declare the @SQL Variable which will hold our dynamic sql
    DECLARE @SQL VARCHAR(8000), @SQL2 VARCHAR(8000), @SQL3 VARCHAR(8000)
    SET @SQL = ”;
    SET @SQL2 = ”;
    SET @SQL3 = ”;
    — Declare the @TblName Variable which will hold the name of the current table
    DECLARE @TblName VARCHAR(255);

    — Open the Cursor
    OPEN TBL_CURSOR

    — Setup the Fetch While that will loop through our cursor and set @TblName
    FETCH NEXT FROM TBL_CURSOR INTO @TblName
    — Do this while we are not at the end of the record set
    WHILE (@@FETCH_STATUS -1)
    BEGIN
    — Appeand this table’s select count statement to our sql variable
    IF ASCII (@TblName) % 3 = 0
    SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

    IF ASCII (@TblName) % 3 = 1
    SET @SQL2 = @SQL2 + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

    IF ASCII (@TblName) % 3 = 2
    SET @SQL3 = @SQL3 + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

    — Pull the next record
    FETCH NEXT FROM TBL_CURSOR INTO @TblName
    — End the Cursor Loop
    END

    — Close and Clean Up the Cursor
    CLOSE TBL_CURSOR
    DEALLOCATE TBL_CURSOR

    — Since we were adding the UNION at the end of each part, the last query will have
    — an extra UNION. Lets trim it off.
    –SET @SQL = LEFT(@SQL, LEN(@SQL)-6);
    –SET @SQL2 = LEFT(@SQL2, LEN(@SQL2)-6);
    SET @SQL3 = LEFT(@SQL3, LEN(@SQL3)-6);

    — Lets do an Order By. You can pick between Count and Table Name by picking which
    — line to execute below.
    –SET @SQL = @SQL + ‘ ORDER BY Count’;
    –SET @SQL = @SQL + ‘ ORDER BY Table_Name’;

    — Now that our Dynamic SQL statement is ready, lets execute it.
    EXEC (@SQL + @SQL2 + @SQL3 + ‘ ORDER BY Count’);
    –EXEC (@SQL + @SQL2 + @SQL3 + ‘ ORDER BY Table_Name’);
    GO

    Like

  7. I used this:

    SELECT object_name(id) ,rowcnt
    FROM sysindexes
    WHERE indid IN (1,0) AND OBJECTPROPERTY(id, ‘IsUserTable’) = 1
    order by rowcnt desc

    Like

  8. Hi Justin Carmony,

    I am an Oracle DBA and I wanted something in SQL Server 2005 that is similar to
    PROCEDURE GET_STAGING_INSERT_COUNTS
    IS
    v_ErrorCode integer;
    v_ErrorMessage varchar2(512);
    v_table_name varchar2(100);
    e_countfailure EXCEPTION;
    BEGIN
    .
    .
    .
    for c1rec0 in (select table_name,
    to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘ ||
    table_name)),
    ‘/ROWSET/ROW/C’)) ROW_count
    from user_tables ttl
    ) loop
    begin

    insert_log_data(c1rec0.table_name,
    to_char(sysdate, ‘YYYYMMDDHH24MISS’),
    ‘STG COUNT’, ‘Success’, c1rec0.ROW_count,
    ‘from staging’);
    COMMIT;
    end;
    .
    .
    .

    end loop;
    .
    .
    .
    COMMIT;
    EXCEPTION
    WHEN others THEN
    return_status := ‘Failed’;
    v_errorcode := SQLCODE;
    v_errormessage := sqlerrm;
    insert_ATOS_log_data(v_table_name, to_char(sysdate, ‘YYYYMMDDHH24MISS’), ‘COUNTS’, ‘Failed’, 0, ‘GET_STAGING_INSERT_COUNTS’);
    insert_ATOS_log_data(‘Process Failed’, to_char(sysdate, ‘YYYYMMDDHH24MISS’), ‘COUNTS’, ‘Failed’, 0, ‘Load_Control Error Code: ‘ || v_errorcode || ‘ Error Message: ‘ || v_errormessage);
    END GET_STAGING_INSERT_COUNTS;

    and the above will save me a tremendous amount of time.
    Thank You

    Like

  9. CREATE TABLE DB_TBL_ROWCNT (T_NAME VARCHAR(100), R_COUNT INTEGER)

    insert into db_tab exec sp_tables

    select ‘select ”’+ table_name +”’ ,count(*) from ‘+ table_name + ‘ union’
    from db_tab where table_type=’TABLE’
    –(output of above query ) –> [A]

    INSERT INTO DB_TBL_ROWCNT
    –>[A]

    select * from DB_TBL_ROWCNT

    Like

  10. Jus another way…

    CREATE TABLE DB_TAB (TABLE_QUALIFIER VARCHAR(50),TABLE_OWNER VARCHAR(50), TABLE_NAME VARCHAR(100), TABLE_TYPE VARCHAR(50), REMARKS VARCHAR(50))

    CREATE TABLE DB_TBL_ROWCNT (T_NAME VARCHAR(100), R_COUNT INTEGER)

    insert into db_tab exec sp_tables

    select ‘select ”’+ table_name +”’ ,count(*) from ‘+ table_name + ‘ union’
    from db_tab where table_type=’TABLE’
    –(output of above query ) –> [A]

    INSERT INTO DB_TBL_ROWCNT
    –>[A] (remove the last “union” here)

    select * from DB_TBL_ROWCNT

    Thnx,
    Ram

    Like

  11. exec sp_msforeachtable “select ‘?’ tablename, sum(1) totalrows from ?”

    Like

  12. — I used this:

    select o.name, i.rows, i.indid –, i.*
    from sysindexes i, sysobjects o
    where i.indid < 2
    and o.id = i.id
    and o.type = 'u'
    order by 1

    Like

Leave a Reply to Dos Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close