June 5, 2009 at 10:12 am
Hi all...I'm trying to figure out a way to build a table that will run through my tables in my database and add a row with the following data:
TableName
Total_RowCount
YTD_RowCount
MTD_RowCount
Yesterday_RowCount
I have found
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
online, but the issue is that the statistics on my tables are not accurate, nor are they updated with any frequency (this part of the admin is out of my hands, has been escalated, and has been ignored). For example, I have one table with 13,333,456 rows, but when I use the above query, I get 13,245,348 rows. Slightly off, which is why I'll want to use the live counts. The different columns are so I can then see if the table hasn't been used/updated this year, it may be time to consider restructuring; on the flip side, if it's a highly active table, I can then focus on tuning queries etc. that use that table.
Any ideas would be appreciated...
TIA
Chris
June 5, 2009 at 10:23 am
Count you not just have table that has new rows inserted into each day with the total count(*) at the end or beginning of that day?
That way you could run monthly weekly yearly reports based on the counts for each table.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 5, 2009 at 10:30 am
...yup, that could work...my brain was going down a path and wasn't really thiniking about alternative ways to capture the data.
June 5, 2009 at 10:28 pm
DBCC UPDATEUSAGE(0) is all you need to run before you run the script you built. It'll take a little while to run if you have a huge database but it's still faster than COUNT(*) for each table.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2009 at 10:34 pm
I don't know if it'll be overkill for what you're trying to do, but this will certainly let you know what's going on with your tables. Think of it as sp_SpaceUsed on Steroids. Just add a sample DATE column and change it into an insert.
[font="Courier New"]/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
--SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
[/font]
_____________________________________________________________________________________________________________________________________________________
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2009 at 4:40 am
Hi Jeff - Thanks for the reply...the problem is that according to the agreement my company has with the development company, I cannot do certain activities and updating the stats is one of them. They don't want us having anything to do with the system tables, apparently. This is why I can't use the sysobjects and sysindexes - they are totally inaccurate and as I need to have as accurate a count on each table as possible, this won't work. Some of the tables have never had their stats updated, some have gone more than 2 years. So doing a count on the data in the system tables will just keep returning the same data every time, when in fact these tables could be very heavily used.
What I'm thinking of is something along the lines of
select o.name
into #Temp
from sysobjects o
where o.type = 'u'
--From here, somehow I'd like to do a loop with
insert into TableStats
select getdate(), count(*)
from -- each table name in #Temp
It's this second part that's really giving me trouble.
suggestions appreciated...
Chris
June 12, 2009 at 6:50 am
Update usage does not update stats. It updates the space used metadata. That's what's wrong in this case.
Jeff is 100% correct. You need to run updateusage before running that. If you can't do that, then you'll have to run counts on the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2009 at 7:08 am
darth.pathos (6/6/2009)
It's this second part that's really giving me trouble.suggestions appreciated...
Chris
Do some googling for the undocumented stored proc sp_MSforeachtable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2009 at 7:37 am
sweeeeeet.
exec sp_MSforeachtable 'select ''?'' as TableName, count(*) as TotalRows from ?'
The ? is in two single quotes, not one double quotes. '' not ".
[Chris runs off giggling maniacally to play with his new code]
June 12, 2009 at 7:46 am
#include "Usual caveats and warnings about using undocumented commands"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply