Row count in SQL Server

samspritzer posted 2 years ago in General

Version 9.3.0.4984

When in the database view where all of the objects (tables, functions, procedures, etc.) are listed, there is a column called Rows. I assume this is for displaying the number of rows in that table. If not, how can I view the number of rows without having do a SELECT COUNT(*) from 'TABLE'?

kalvaro posted 2 years ago

MySQL (the DBMS that HeidiSQL was originally written for) maintains a row count that can be queried together with table information at constant cost. I don't think SQL Server provides a similar feature (if it does, though, I shouldn't be a great deal to display it.)

samspritzer posted 2 years ago

Thank you for your reply! I am not sure if its built into SQL Server but its a feature that is used in another program like HeidiSQL. I think it would be an awesome option. I am always constantly monitoring row counts especially when running jobs that populate tables.

igor.zhilin posted 3 months ago

Dear HeidiSQL Enthusiasts and Developers

Row count in MS SQL Server does not work at all.

Can you please include the following proper query for MS SQL that will work with any MS SQL Server:

SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    ,OBJECT_NAME(p.object_id) AS [Table]
    ,sum(p.rows)    AS [Row Count]
FROM sys.partitions p
WHERE 1=1
    and p.index_id in (0,1) /*take heap or clustered index, they are mutually exclusive */
    and OBJECT_NAME(p.object_id) = @tableNameInHeidiSQLList
GROUP BY OBJECT_SCHEMA_NAME(p.object_id)
        ,OBJECT_NAME(p.object_id)

Would be easier if there were a separate settings file containing the SQL queries that HeidiSQL uses for row counts, object lists etc. Then I would be able to adjust the row count query.

Please let me know if you can add this query to HeidiSQL.

Please login to leave a reply, or register at first.