SP for row count of tables

Question

Post reply

SP for row count of tables

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

To store the results of the query into a variable you can do something like:

DECLARE @body varchar(8000)

SET @body = ''

SELECT @body = @body + s.name + '.' + t.name + CHAR(9) + CAST(SUM(ps.row_count) AS varchar(9)) + CHAR(13)

FROM sys.tables t

INNER JOIN sys.schemas s oN t.schema_id = s.schema_id

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id

WHERE i.index_id IN (0,1)

GROUP BY s.name, t.name, i.name

ORDER BY s.name, t.name

Ugly code but it seems to work

VRT SSCertifiable Points: 5776 More actions · Answer 2

Thanks Chris. It is working fine and able to send the mails with the table counts

Thank You

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

The rowcounts in the SysIndexes table may not be anywhere near accurate until you run DBCC UPDATEUSAGE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VRT SSCertifiable Points: 5776 More actions · Answer 4

Chris Harshman (12/5/2008)
SELECT @body = @body + s.name + '.' + t.name + CHAR(9) + CAST(SUM(ps.row_count) AS varchar(9)) + CHAR(13) FROM sys.tables t INNER JOIN sys.schemas s oN t.schema_id = s.schema_id INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id WHERE i.index_id IN (0,1) GROUP BY s.name, t.name, i.name ORDER BY s.name, t.name
Ugly code but it seems to work

sys.dm_db_partition_stats - is giving the following error.

The user does not have permission to perform this action.

Is The above system table must be used by DBA only?

Thank You

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Books Online says that querying this table requires the permission "VIEW DATABASE STATE", so they don't need to be a DBA, just have this granted to them.:

http://msdn.microsoft.com/en-us/library/ms187737.aspx

VRT SSCertifiable Points: 5776 More actions · Answer 6

Chris Harshman (12/8/2008)
Books Online says that querying this table requires the permission "VIEW DATABASE STATE", so they don't need to be a DBA, just have this granted to them.:

I have the access as below. this looks like I don't have the above access. Please see the attachment.

Thank You

VRT SSCertifiable Points: 5776 More actions · Answer 7

venki (12/9/2008)
Chris Harshman (12/8/2008)
Books Online says that querying this table requires the permission "VIEW DATABASE STATE", so they don't need to be a DBA, just have this granted to them.:
I have the access as below. this looks like I don't have the above access. Please see the attachment.

Can any one check this and tell me why I am not able to access the system table sys.dm_db_partition_stats ?

Thank You

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

venki (12/10/2008)
Can any one check this and tell me why I am not able to access the system table sys.dm_db_partition_stats ?

Is the DBA unwilling to grant the VIEW DATABASE STATE permission to you? If so, that will limmit your options here.

VRT SSCertifiable Points: 5776 More actions · Answer 9

Is the DBA unwilling to grant the VIEW DATABASE STATE permission to you? If so, that will limmit your options here.

I am not sure I have this Permission. I attached a screen shot of my account access. DBA provided my account as Public user. Please see the attachment in above post.

Please let me know how can I see the permissions for my account to that SQL Server 2005? There were so many databases but I have the access only one database which is belong to my project?

Thanks a lot,

Venki.

Thank You

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

I'd just use a query like this:

Select

S.name as schema_name,

O.name as table_name,

P.rows ,

O.type

from

sys.partitions P join

sys.tables O on

P.object_id = O.object_id Join

sys.schemas S On

O.schema_id = S.schema_id

Where

P.index_id in (0,1)-- heap or clustered index

No special permissions required. I don't know if sys.partitions is more reliable than sysindexes or not.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question