View SQL Server data space allocation

Source: Internet
Author: User
Tags reserved rtrim table name
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.