View SQLServer data space allocation. Today, the customer reported that the database file space is growing too fast. He needs to analyze the distribution of the database table storage space and temporarily write the following process to share with you. * ************************ Today, the customer reported that the database file space is growing too fast, you need to analyze the storage space allocation of database tables and temporarily write the following process,
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
--Knowsky.com 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
Note:
Explain, you need to analyze the distribution of storage space in the database table, temporarily write the following process, share with you. /**************************...
