Sunday, November 2, 2014

Creating a Stored procedure to get sizes of all the tables in a database using sp_spaceused

CREATE PROCEDURE pGetAllTableSizes
AS
BEGIN

Declare @tableName varchar(100)

Declare tblCursor Cursor
For
select [name] from dbo.sysobjects
where ObjectProperty(id,N'IsUserTable')=1
For read only

Create table #tmpTable
(
tableName varchar(100),
numberofrows varchar(100),
reservedsize varchar(100),
datasize varchar(100),
indexsize varchar(100),
unusedsize varchar(100)
)

open tblCursor

Fetch Next from tblCursor into @tableName
While (@@FETCH_STATUS>=0)
Begin
Insert #tmpTable
exec sp_spaceused @tableName

Fetch Next from tblCursor into @tableName
End
close tblCursor 
deallocate tblCursor 

select tableName,numberofrows, reservedsize , datasize, indexsize, unusedsize from #tmpTable
order by reservedsize desc

drop table #tmpTable

END
GO



No comments :

Post a Comment