July 29, 2003 at 1:26 pm
Someone told me that there was a stored procedure that would return the physical size of the table on the hard disk. Does anyone know of this stored procedure?
I could get a ballpark figure by adding up the column widths multiplied by the number of rows, but I have VARCHARs and TEXT columns in some of the tables, so it wouldn't be very accurate.
TIA
AndreQ
July 29, 2003 at 1:28 pm
Look up sp_spaceused in books online. I think that will give you what you want.
Tom
July 29, 2003 at 1:31 pm
You could even see the size of the tables in Enterprise Manager --> View Task Pad --> Tables Page...
.
July 29, 2003 at 1:53 pm
Look up sp_spaceused in books online. I think that will give you what you want.
Tom
July 30, 2003 at 2:02 am
I wrote this script only recently to use the sp_spaceused stored proc to return the space data for each table in the current database, the output is ordered in ascending size of reserved space for each table:
BEGIN
CREATE TABLE #tspace
([name]VARCHAR(100),
[rows]INT,
[reserved] VARCHAR(50),
[data]VARCHAR(50),
[index_size] VARCHAR(50),
[unused] VARCHAR(50))
DECLARE cTables CURSOR FOR
SELECT u.[name] + '.' + o.[name]
FROM sysobjects o, sysusers u
WHERE u.uid = o.uid AND o.type = 'U'
DECLARE @tname VARCHAR(255)
OPEN cTables
FETCH NEXT FROM cTables INTO @tname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tspace EXEC sp_spaceused @tname
FETCH NEXT FROM cTables INTO @tname
END
CLOSE cTables
DEALLOCATE cTables
SELECT * FROM #tspace
ORDER BY CAST(LEFT([reserved],CHARINDEX(' ',[reserved])) AS INT)
DROP TABLE #tspace
END
Dave Leathem.
It's just what we asked for but not what we want! (The Customer's Creed)
July 30, 2003 at 3:38 am
Another approach would be using the undocumented sp_MSforeachtable:
create table #spacedata
(t_name varchar(100),
t_rows int,
t_reserved varchar(50),
t_data varchar(50),
t_idxsize varchar(50),
t_unused varchar(50))
GO
sp_MSforeachtable
@command1 = "insert into #spacedata EXEC sp_spaceused '?'"
July 30, 2003 at 1:46 pm
I came up with this script (based on sp_spaceused) to show the size of tables and their indexes. If you haven't run DBCC UPDATEUSAGE, some of the numbers won't add up (actual size greater than allocated size).
SELECT a.TableName, DataMB, TotalMB, IndexName, IndexMB, IndexAllocMB
FROM (
select o.name as TableName, STR(i.dpages * 0.0078125,7,1) as DataMB, STR(i.used * 0.0078125,7,1) as TotalMB
from sysobjects o
inner join sysindexes i on o.id = i.id
where o.xtype = 'u' and o.status > 0 and i.indid <= 1
) A
INNER JOIN (
select o.name as TableName, i.name as IndexName, STR(i.dpages * 0.0078125,7,1) as IndexMB, STR(i.used * 0.0078125,7,1) as IndexAllocMB
from sysobjects o
inner join sysindexes i on o.id = i.id
where o.xtype = 'u' and o.status > 0 and i.indid between 2 and 254 and i.used > 0
) B
ON A.TableName = B.TableName
ORDER BY TotalMB DESC, IndexAllocMB DESC
July 30, 2003 at 1:50 pm
Oops, make that a LEFT JOIN.
And if you only want table sizes (or only one row per table), remove subquery B.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply