Returning number of rows in a table and table sizes
-
Hi all,
can someone tell me how to return the number of rows and the sizes and other similar information.
I am coding a maintenance screen which returns the tables in my DB and I want to show against each row the size, number of rows etc.
Any help much appreciated.
Thanks.
CCB
-
Provided your index statistics are up-to-date and you have a primary key and/or clustered index on your table then the following query is the quickest way of getting a row count.
SELECT MAX(rowcnt) AS Customers
FROM dbo.sysindexes
WHERE id = Object_Id( 'dbo.Customers')
AND indid IN (0,1)
Row sizes are a bit more complex but there was a script somewhere on this site that calculated these and downloaded them to a spreadsheet. Sorry I can't be more specific.
-
I would suggest looking up INFORMATION_SCHEMA in Books OnLine (BOL). Here is a simple example of things it can offer you. You may find you will need to use other system information tables. Good luck.
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
INTO #TableInformation
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME IN( 'Address', 'terms_tilde', 'payments_raw', 'paymentdate_raw',
'vendors_tilde', 'vouchers_raw')
I wasn't born stupid - I had to study.
-
Hi,
I hoped I could run some t-sql to return the following: -
TableName Rows Size
tblAddress 55 1Mb
tblNames 1234 5.98Mb
tblContacts 402 0.87Mb
I have started with: -
Select A.name From sysobjects A Where Upper(A.name) Like 'TBL%'
Any Ideas.
Thanks
CCB
-
Try playing with this... It gives you more information than you want...
SET NOCOUNT ON
SELECT name AS TableName
INTO #TableName
FROM sysobjects
WHERE xtype IN( 'U', 'S') -- not 'S or system table
AND name NOT LIKE 'sys%'
-- AND name NOT IN( 'dir04_LAB', 'sub_human_code')
AND UID = 1.0 -- Indicates a dbo created table
CREATE TABLE #TableInformation(
TableName nvarchar(35),
RowsCount char(20),
SpaceReserved varchar(25),
SpaceUsed varchar(25),
IndexSize varchar(25),
SpaceUnused varchar(25))
DECLARE @sql nvarchar(2000),
@TableName varchar(100)
DECLARE TableInformation INSENSITIVE CURSOR FOR
SELECT TableName
FROM #TableName
WHERE TableName IS NOT NULL
ORDER BY TableName ASC
OPEN TableInformation
next_record:
FETCH NEXT FROM TableInformation INTO @TableName
IF @@FETCH_STATUS = 0
BEGIN
IF ISNULL( @TableName, 'N/A') <> 'N/A'
BEGIN
INSERT INTO #TableInformation
EXEC sp_spaceused @TableName --, @updateusage = 'TRUE' -- when the update statics parameter is run, no records returned
END
GOTO next_record
END
CLOSE TableInformation
DEALLOCATE TableInformation
SELECT TableName AS 'Table Name', CONVERT( integer, REPLACE( SpaceUsed, ' KB', '')) AS 'Space Used KB',
CONVERT( integer, REPLACE( SpaceReserved, ' KB', '')) AS 'Space Reserved KB',
CONVERT( integer, REPLACE( SpaceUnused, ' KB', '')) AS 'Space Unused KB',
CONVERT( integer, RowsCount) AS 'Rows Count', CONVERT( integer, REPLACE( IndexSize, ' KB', '')) AS 'Index Size KB'
FROM #TableInformation
ORDER BY TableName ASC
DROP TABLE #TableName
DROP TABLE #TableInformation
I wasn't born stupid - I had to study.
-
CREATE TABLE #TableSize(
TableName SysName,
NumOfRows Int ,
Reserved VARCHAR(10),
Data VARCHAR(10),
IndexSize VARCHAR(10),
Unused VARCHAR(10)
)
DECLARE @sNextTable SysName
SET @sNextTable=''
WHILE @sNextTable IS NOT NULL
BEGIN
SELECT @sNextTable = MIN(Name)
FROM dbo.SysObjects
WHERE Type='U'
AND Name > @sNextTable
IF @sNextTable IS NOT NULL
INSERT #TableSize
exec sp_spaceused @sNextTable
END
SELECT * FROM #TableSize
DROP TABLE #TableSize
-
Perfect. Do users need any special permissions to run this ?Thanks again.
CCB
-
Not as the script stands.
sp_spaceused has an @updateusage parameter and to use that you either need to be the dbo or in the sysadmin role.
I've not used it here so there should be no problem.
As the stored procedure returns information about your tables perhaps it should be restricted.
-
Check out the column called "AverageRecordSize" in the following...
DBCC UPDATEUSAGE (0)
DBCC SHOWCONTIG WITH TABLERESULTS
--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) -
Here's another way to skin the cat which includes usage for Text/Image data
-- if you want to get the statistics updated, uncomment the next 2 Lines--DBCC UPDATEUSAGE (0)
--go
SELECT so.name as TableName,
case when si.indid=255 then 'Image_Text Info' when si.indid <2 then 'Data Info' end [Data Type],
case when si.indid=255 then 'N/A' when si.indid <2 then cast(si.rowcnt as varchar(20)) end [Rows],
si.reserved/128.0 [Allocated_MB],
si.used/128.0 [Used_MB]
FROM sysobjects so
JOIN sysindexes si
ON si.id = so.id
WHERE si.indid <2 or si.indid= 255 and so.Type='U' AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0
order by so.Name
Note that it is possible to have zero rows and still have pages allocated (if a table is empty but not Truncated). Also the "Rows" for Text/Image data is not relevant (would always show 0) even if there are pages allocated for handling this type of data.
Toni
-
Ooops... should have corrected the query for proper order of processing in the Where clause..
SELECT so.name as TableName,case when si.indid=255 then 'Image_Text Info' when si.indid <2 then 'Data Info' end [Data Type],
case when si.indid=255 then 'N/A' when si.indid <2 then cast(si.rowcnt as varchar(20)) end [Rows],
si.reserved/128.0 [Allocated_MB],
si.used/128.0 [Used_MB]
FROM sysobjects so
JOIN sysindexes si
ON si.id = so.id
WHERE (si.indid <2 or si.indid= 255) and so.Type='U' AND OBJECTPROPERTY(so.ID,'IsMSShipped') = 0
order by so.Name
Apologies
Toni
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply