View SQL Server data space allocation

server| data today, customers reflect the growth of database file space is too fast, need to analyze the database table storage space allocation, temporarily wrote the following process,

Share with you.

/********************************
Function: Get the space distribution of the table Ycsoft 2005-07-13
**********************************/

If not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Tablespaceinfo] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
CREATE TABLE Tablespaceinfo--Creating a result store table
(Nameinfo varchar (50),
rowsinfo int, reserved varchar (20),
Datainfo varchar (20),
Index_size varchar (20),
Unused varchar (20))

Delete from Tablespaceinfo--empty data table

DECLARE @tablename varchar (255)--table name

DECLARE @cmdsql varchar (500)

DECLARE info_cursor Cursor FOR
Select O.name
From Dbo.sysobjects o where ObjectProperty (o.id, N ' istable ') = 1
and o.name not like N ' #%% ' ORDER by O.name

OPEN Info_cursor

FETCH NEXT from Info_cursor
Into @tablename

While @ @FETCH_STATUS = 0
BEGIN

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (@tablename) and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
Execute sp_executesql
N ' INSERT into Tablespaceinfo exec sp_spaceused @tbname ',
N ' @tbname varchar (255) ',
@tbname = @tablename

FETCH NEXT from Info_cursor
Into @tablename
End

Close Info_cursor
Deallocate info_cursor
Go

--knowsky.com Database Information
sp_spaceused @updateusage = ' TRUE '

--Table information
SELECT *
From Tablespaceinfo
Order BY CAST (LTrim (RTrim (reserved)), Len (LTrim (RTrim (Reserved))-2) as int) desc

Note:

Name	nvarchar (20)	The name of the table for which to request space usage information.
Rows	CHAR (11)	The number of existing rows in the table.
Reserved	varchar (18)	The total amount of space reserved by the table.
Data	varchar (18)	The amount of space used by the data in the table.
Index_size	varchar (18)	The amount of space used by the indexes in the table.
Unused	varchar (18)	The amount of unused space in the table.