December 5, 2008 at 10:01 am
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
December 6, 2008 at 3:32 am
Thanks Chris. It is working fine and able to send the mails with the table counts
Thank You
December 6, 2008 at 4:26 pm
The rowcounts in the SysIndexes table may not be anywhere near accurate until you run DBCC UPDATEUSAGE.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2008 at 12:42 am
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
December 8, 2008 at 8:39 am
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.:
December 9, 2008 at 1:34 am
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
December 10, 2008 at 6:13 am
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
December 10, 2008 at 8:47 am
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.
January 6, 2009 at 12:12 am
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
January 6, 2009 at 12:48 am
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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply