--**************************************
-- Name: GetTableSpace
-- Description:This stored procedure returns the list of tables with number of rows, data space used and index space used numbers. It is using an undocumented sp_Mstablespace stored procedure, which is a super fast alternative to select count(*) from TableName.
-- By: Igor Krupitsky
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1044&lngWId=5--for details.--**************************************
Create Procedure GetTableSpace
as
SET NOCOUNT ON
create table #TableSpace
(
Rows int,
DataSpaceUsed int,
IndexSpaceUsed int
)
declare @TableSpace table
(
TableName varchar(255),
Rows int,
DataSpaceUsed int,
IndexSpaceUsed int
)
declare @Rows int, @DataSpaceUsed int, @IndexSpaceUsed int
Declare @TableName varchar(255)
DECLARE Table_Cursor CURSOR FOR
select user_name(o.uid) + '.' + o.name as table_name
from dbo.sysobjects o, dbo.sysindexes i
where OBJECTPROPERTY(o.id, N'IsTable') = 1
and i.id = o.id
and i.indid < 2
and o.name not like N'#%'
and xtype = 'U'
order by 1
OPEN Table_Cursor
---------------------------------
--Set Data
FETCH NEXT FROM Table_Cursor INTO @TableName
INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
exec sp_MStablespace @TableName
SELECT @Rows = Rows,
@DataSpaceUsed = DataSpaceUsed,
@IndexSpaceUsed = IndexSpaceUsed
FROM #TableSpace
INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)
delete from #TableSpace
--------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
---------------------------------
--Set Data
FETCH NEXT FROM Table_Cursor INTO @TableName
INSERT INTO #TableSpace (Rows, DataSpaceUsed, IndexSpaceUsed)
exec sp_MStablespace @TableName
SELECT @Rows = Rows,
@DataSpaceUsed = DataSpaceUsed,
@IndexSpaceUsed = IndexSpaceUsed
FROM #TableSpace
INSERT INTO @TableSpace (TableName, Rows, DataSpaceUsed, IndexSpaceUsed)
VALUES (@TableName, @Rows, @DataSpaceUsed, @IndexSpaceUsed)
delete from #TableSpace
--------------------------------
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
drop table #TableSpace
select *
from @TableSpace
ORDER BY Rows DESC
|
