January 28, 2011 at 8:26 am
.
SQL DBA.
January 28, 2011 at 8:47 am
🙂 Hi,
How about just right clicking a DB table and select properties, then under Select a page, choose Storage and on the right side of the screen you will see Row count. This also shows data space and index space.
FYI.
January 28, 2011 at 9:05 am
Enjoyed the article and the comments.
January 28, 2011 at 9:09 am
To the last 2 posters:
1) sysindexes will not be supported in future releases.
2) This was suggested as a quick and easy way to report the # of rows in every table in a database. Right-clickig every table isn't practical.
I think we've beat this horse to death, haven't we?
Main points made: It's a nice artical if you want to learn more about DMVs or are interested in the number of rows in every table in your database, but there are other options for getting at the # of rows in a particular table that are considrably simpler.
🙂
January 28, 2011 at 12:08 pm
nice article but i must say nothing innovative about it ,,,,,,,,
many sql server user's already knows it,,,,,, as i said many not all so nice n easy article 😛
[font="Comic Sans MS"]Rahul:-P[/font]
January 29, 2011 at 7:49 am
http://www.sqlservercentral.com/Forums/Topic895882-146-1.aspx
This article states that sysindexes gives an approximate value, which is as documented with BOL. The ssms reports also use this, which can get very different results to the actuals. simply verifying that your approach gives the same result as select count(*) for a single db is hardly good enough testing to suggest we all use this approach. You might want to check your own forums before publishing and misleading, and also msdn docs.
January 31, 2011 at 8:21 am
for myself i use the index scanning DMV and just look at the leaf level for the row count
February 1, 2011 at 3:49 am
I'm not sure if this is interesting for programmers, for DBA's sure but programmers are likely to have limited permissions. It is likely they do not have rights to interrogate systemtable or DMV's.
just a remark 🙂
February 1, 2011 at 8:15 am
Interesting article Kendal, I have been using the same dmv as you have mentioned here but with a slight modification and here it is
Select Object_Name(ddps.object_id) TableName
, ddps.row_count #Rows
From sys.dm_db_partition_stats ddps
Where ObjectProperty(ddps.object_id, 'IsUserTable') = 1
And ddps.index_id < 2
Order By Object_Name(ddps.object_id)
February 1, 2011 at 5:31 pm
This is an old, long thread and maybe it has already been mentioned but you can customize and add the RowCount column in the Object Explorer Details view for 'tables' in SQL 2008 SSMS.
So how does SSMS get it?
Like this:
(edited out of a Profiler trace)
select tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
FROM
sys.tables AS tbl
February 2, 2011 at 7:22 am
dmigo (2/1/2011)
This is an old, long thread and maybe it has already been mentioned but you can customize and add the RowCount column in the Object Explorer Details view for 'tables' in SQL 2008 SSMS.So how does SSMS get it?
Like this:
(edited out of a Profiler trace)
select tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
FROM
sys.tables AS tbl
how long does this take to run compared to select count(*)?
we have some daily reports that give us the row counts in publishers and subscribers that take a long time to run sometimes on tables with tens of millions of rows
February 2, 2011 at 9:41 am
No one has mentioned this method:
select * from tableA
then scroll down to the bottom to see how many rows there are
:Whistling::w00t::hehe::-D
February 8, 2011 at 11:59 am
homebrew01 (2/2/2011)
No one has mentioned this method:select * from tableA
then scroll down to the bottom to see how many rows there are
:Whistling::w00t::hehe::-D
Evil :-D:w00t::hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2011 at 12:01 pm
CirquedeSQLeil (2/8/2011)
homebrew01 (2/2/2011)
No one has mentioned this method:select * from tableA
then scroll down to the bottom to see how many rows there are
:Whistling::w00t::hehe::-D
Evil :-D:w00t::hehe:
I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:
February 8, 2011 at 12:19 pm
Ninja's_RGR'us (2/8/2011)
CirquedeSQLeil (2/8/2011)
homebrew01 (2/2/2011)
No one has mentioned this method:select * from tableA
then scroll down to the bottom to see how many rows there are
:Whistling::w00t::hehe::-D
Evil :-D:w00t::hehe:
I'd just do SELECT * FROM dbo.TblA ORDER BY 1 DESC. It's even faster :hehe::w00t:
Where are the cursor or recursive cte methods for this?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 91 through 105 (of 108 total)
You must be logged in to reply to this topic. Login to reply