March 19, 2009 at 4:55 am
I have tried to search for count sql and have seen the usual using Information_Schema or sys.indexes.
My problem is I don't know how to specify particular tables
I have the following table in the database
MyDBCount with columns TableName, CurrentRowCount, PrevRowCount
I would like a script that loops through only the tablenames in this table and populates the CurrentRowCount.
Then update the PrevRowCount and clear out the CurrentRowCount
How easy would this be?
March 19, 2009 at 7:16 am
very easy, just plan it out.
1. take any of the scripts that count rows from sysindexes. no need to filter which tables at this point.
2. put the results in a temporary table.
3. update your table from that...tables that you do not care about will simply not appear:
ie
UPDATE MyDBCount
SET PrevRowCount = CurrentRowCount, --set to the previous value
CurrentRowCount = #TMP.rows --set to the current value
FROM #TMP
WHERE MyDBCount.TableName = #TMP.TableName
Lowell
March 19, 2009 at 8:12 am
There are a few hundred tables and I only need info on about 50.
Looping through an extra 150 tables (some of which are quite large) would take too long.
Also, most of the tables do not have indexes and therefore wouldn't exist in the indexes table?
March 19, 2009 at 8:27 am
Michael (3/19/2009)
There are a few hundred tables and I only need info on about 50.Looping through an extra 150 tables (some of which are quite large) would take too long.
Also, most of the tables do not have indexes and therefore wouldn't exist in the indexes table?
looping is the key here.
yes, if we loop by doing SELECT COUNT(*) FROM EACHTABLE, it would be faster for 50 tables than 200...but we don't loop, we get teh data via a set based solution straight from some indexes, which already have the count..
this script is a great example:
SET STATISTICS IO ON
SET STATISTICS TIME ON
Select
S.name as schemaname,
T.name as tablename,
P.rows
from
sys.partitions P Join
sys.tables T On
P.object_Id = T.object_id Join
sys.schemas S On
T.schema_id = S.schema_id
Where
P.index_id in (0,1)
this
even tables with no defined index will show up...
since it's doing a scan of a table, the performance of grabbing 150 rows vs only 50 rows is insignificant...they take the same amount of time....actually, filtering to remove 100 specific tables would slow it down. you can test this yourself, as i've included the SET STATISTICS IO and TIME commands; my 1600 table database returns results in 56 milliseconds for all tables.
so it's a huge advantage to just grab all the tables, and update your table by table name for only the items you want to track.
Lowell
March 19, 2009 at 8:33 am
How would I incorporate that code to count the table rows? :unsure:
Ignore - Hadn't ran the code!!
Is this an estimate or the exact number of rows, read somewhere about DBCC UPDATEUSAGE??
Or is this to do with something else??
Quite a novice with SQL if you hadn't guessed...:-P
March 19, 2009 at 8:38 am
Hi Michael
This should get you started. It is a basic example, I did not implement any kind of error management so please test extensively!
DECLARE@TableName SYSNAME
DECLARE @SQL VARCHAR(MAX)
DECLARE @RowCount INT
DECLARE cr_Tables CURSOR FAST_FORWARD FOR
SELECT TableName FROM MyDBCount
OPEN cr_Tables
FETCH NEXT FROM cr_Tables
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE MyDBCount '
SET @SQL = @SQL + 'SET PrevRowCount = CurrentRowCount'
SET @SQL = @SQL + ',CurrentRowCount = (SELECT Rows = COUNT(*) FROM ' + @TableName + ')'
SET @SQL = @SQL + ' WHERE TableName = ''' + @TableName + ''''
EXEC(@SQL)
FETCH NEXT FROM cr_Tables
INTO @TableName
END
CLOSE cr_Tables
DEALLOCATE cr_Tables
SELECT * FROM MyDBCount
Let me know how this works for you.
March 19, 2009 at 8:40 am
simply run the script. it counts the table rows,even for tables with no indexes or primary keys (heap tables).
so you could potentially do something like this:
UPDATE MyDBCount
SET PrevRowCount = CurrentRowCount, ---set to the previous value
CurrentRowCount = MySubSet.row ---set to the current value
FROM (
Select
S.name as schemaname,
T.name as tablename,
P.rows
from
sys.partitions P Join
sys.tables T On
P.object_Id = T.object_id Join
sys.schemas S On
T.schema_id = S.schema_id
Where
P.index_id in (0,1)
) MySubSet
WHERE MyDBCount.TableName = MySubSet.TableName
Lowell
March 19, 2009 at 8:45 am
Hi again
I saw Lowell's post after replying...
How reliable is the count in sys.partitions because a solution based on that will definitly beat a cursor on speed!
March 19, 2009 at 8:45 am
Maxim's code will work, and would obviously only run for the tables that exist in your counting table...
but if you compare it to the set based solution, it'll be a little bit slower, since it is doing row by row processing.
my set based solution is faster, and will not slow down as you track more and more tables.
Lowell
March 19, 2009 at 8:49 am
Maxim Picard (3/19/2009)
Hi againI saw Lowell's post after replying...
How reliable is the count in sys.partitions because a solution based on that will definitly beat a cursor on speed!
Maxim's right, unless you run DBCC UPDATEUSAGE prior to running my snippet, the indexes could be off by a number of rows....i've never seen it off by more than a few percent. but 1% on a million row table could be a big difference...
i've got a script laying around that compared actual counts like Maxim did vs the index's counts....let me find it and I'll let you know the max differences i could find.
Lowell
March 19, 2009 at 8:56 am
here's my comparison snippet for SQL 2000:
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
--tablename is [dbo].[TBLNAME] instead of plain 'TBLNAME'
update #rowcount set tablename = replace(tablename,'[dbo].[','')
update #rowcount set tablename = replace(tablename,']','')
select * from #rowcount
LEFT OUTER JOIN (
SELECT sysobjects.name, MAX(sysindexes.rows) AS NUMROWS
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'U'
GROUP by sysobjects.name
--ORDER BY NUMROWS DESC,sysobjects.name
) X ON #rowcount.tablename = X.name
where #rowcount.rowcnt <> X.NUMROWS
order by tablename
Lowell
March 19, 2009 at 9:36 am
Neither Code runs very well - Cancelled both after 10 minutes..
Your first code snippet works - Would I need to run DBCC UPDATEUSAGE before I ran it?
What would I need to include?
DBCC UPDATEUSAGE ('MyDatabase')
or
DBCC UPDATEUSAGE ('MyDatabase')WITH COUNT_ROWS
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply