How to get the occupied capacity of a table in a SQL Server database_PHP tutorial

In fact, as long as you use the system's built-in stored procedure sp_spaceused, you can get the relevant information of the table
For example: sp_spaceused 'tablename'
The following is a stored procedure written for convenience. The purpose is to get all the relevant information of all current tables. Save in a specified table
CREATE PROCEDURE get_tableinfo AS

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id , N'IsUserTable') = 1)
create table tablespaceinfo --Create the 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 @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


Execute stored procedure
exec get_tableinfo
query The result obtained after running this stored procedure is
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved))), len(ltrim(rtrim(reserved)))-2) as int ) desc

http://www.bkjia.com/PHPjc/631066.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631066.htmlTechArticleIn fact, as long as you use the system's built-in stored procedure sp_spaceused, you can get the relevant information of the table, such as: sp_spaceused 'tablename' The following is A stored procedure written for convenience, the purpose is to...