September 1, 2003 at 4:41 am
I am having problems with counting records on a number of table in a Database.
I have created this script.
CREATE TABLE TBLSize1
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @DBname varchar(80)
DECLARE @tablename varchar(80)
SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TBLSize1(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @tablename
-- Get the next table.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM TBLSize1 where tblname in (
'table_1',
'table_2',
'table_3',
'table_4',
'table_5',
'ETC')
Order by Tblname
It is putting table counts into a table and I am then using this info. I have found out lately that the table counts are sometimes different to when I manually run the ‘select count(*) from table_1’. How does the ‘Sp_SpaceUsed’ SP work? Is this a problem with updating Stats.
If anyone needs anymore info please ask.
Thanks in advance
September 1, 2003 at 6:39 am
Check DBCC UPDATEUSAGE in BOL.
CVM.
September 1, 2003 at 6:52 pm
Hi there
This routines used are unreliable. The updateusage command is a good one before your routine (in a quiet period). Also take a look at sysindexes and the row column, see BOL for help. I believe the scripts section of this site has lots of table row count examples to rip 🙂
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply