Today, the customer reported that the database file space is growing too fast and needs to analyze the distribution of the database table storage space. The following process was written temporarily,
Share with you.
/********************************
Function: obtains the table space distribution.
**********************************/
If not exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [tablespaceinfo] ') and OBJECTPROPERTY (id, n' isusertable') = 1)
Create table tablespaceinfo -- create a result storage table
(Nameinfo varchar (50 ),
Rowsinfo int, reserved varchar (20 ),
Datainfo varchar (20 ),
Index_size varchar (20 ),
Unused varchar (20 ))
Delete from tablespaceinfo -- clear the data table
Declare @ tablename varchar (255) -- table name
Declare @ brief SQL varchar (500)
DECLARE Info_cursor CURSOR
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
--111cn.net database information
Sp_spaceused @ updateusage = 'true'
-- Table information
Select *
From tablespaceinfo
Order by cast (left (ltrim (rtrim (reserved), len (ltrim (rtrim (reserved)-2) as int) desc
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.