ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

Space used by database objects

Categories: SQL Server
CREATE TABLE #foo(
name varchar(128),
rows int,
reserved varchar(128),
data varchar(128),
index_size varchar(128),
unused varchar(128)
)
DECLARE @objectName nvarchar(128)
DECLARE cursorTables CURSOR FOR
 SELECT so.name
 FROM sysobjects so (nolock), sysindexes si (nolock)
 WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
 GROUP BY so.name 
 ORDER BY MAX(si.rows) DESC
OPEN cursorTables
FETCH NEXT FROM cursorTables INTO @objectName
WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO #foo
 EXEC ('sp_spaceused ' + @objectName)
 FETCH NEXT FROM cursorTables INTO @objectName
END
CLOSE cursorTables
DEALLOCATE cursorTables
SELECT * FROM #foo
DROP TABLE #foo