About checking the space occupied by each table of MSSQL database users

About checking the space occupied by each table of MSSQL database users_PHP tutorial

WBOY

Release： 2016-07-21 15:04:22

Original

1232 people have browsed it

In a recent project, I needed to check the size of the data user table, including the number of records and the amount of disk space occupied. I have been searching online for a long time to check the space occupied by each table in the MSSQL database. It is relatively okay.
However, methods 2 and 3 return a lot of data, some of which are data we don’t care about. I did the test on AdventureWorks2012 data. The code for the second method is as follows:

Copy code The code is as follows:

View Code 
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --Create 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 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
--itlearner Note: Display database information 
sp_spaceused @updateusage = 'TRUE' 
--itlearner Note: Display table information
select * 
from tablespaceinfo 
order by cast(left(ltrim(rtrim(reserved)), len(ltrim(rtrim(reserved)))-2) as int) desc
 

The running effect is as follows:

Obviously this return result is wrong. But it provides an idea. The modified SQL statement is as follows:

Copy the code The code is as follows:

View Code 
IF NOT EXISTS ( SELECT  *
                FROM    sys.tables
                WHERE   name = 'tablespaceinfo' ) 
    BEGIN
        CREATE TABLE tablespaceinfo --创建结果存储表
            (
              Table_Name VARCHAR(50) ,
              Rows_Count INT ,
              reserved INT ,
              datainfo INT ,
              index_size INT ,
              unused INT
            )
    END 
DELETE  FROM tablespaceinfo
 --清空数据表
CREATE TABLE #temp --创建结果存储表
    (
      nameinfo VARCHAR(50) ,
      rowsinfo INT ,
      reserved VARCHAR(20) ,
      datainfo VARCHAR(20) ,
      index_size VARCHAR(20) ,
      unused VARCHAR(20)
    )
DECLARE @tablename VARCHAR(255)
 --表名称
DECLARE @cmdsql NVARCHAR(500)
DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS Table_Name
    FROM    [INFORMATION_SCHEMA].[TABLES]
    WHERE   TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME <> 'tablespaceinfo'
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
            + ''''
        EXECUTE sp_executesql @cmdsql
        FETCH NEXT FROM Info_cursor
INTO @tablename
    END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注：显示数据库信息
--sp_spaceused @updateusage = 'TRUE'
--itlearner注：显示表信息
UPDATE  #temp
SET     reserved = REPLACE(reserved, 'KB', '') ,
        datainfo = REPLACE(datainfo, 'KB', '') ,
        index_size = REPLACE(index_size, 'KB', '') ,
        unused = REPLACE(unused, 'KB', '')
INSERT  INTO dbo.tablespaceinfo
        SELECT  nameinfo ,
                CAST(rowsinfo AS INT) ,
                CAST(reserved AS INT) ,
                CAST(datainfo AS INT) ,
                CAST(index_size AS INT) ,
                CAST(unused AS INT)
        FROM    #temp
DROP TABLE #temp
SELECT  Table_Name ,
        Rows_Count ,
        CASE WHEN reserved > 1024
             THEN CAST(reserved / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(reserved AS VARCHAR(10)) + 'KB'
        END AS Data_And_Index_Reserved ,
        CASE WHEN datainfo > 1024
             THEN CAST(datainfo / 1024 AS VARCHAR(10)) + 'Mb'
             ELSE CAST(datainfo AS VARCHAR(10)) + 'KB'
        END AS Used ,
        CASE WHEN Index_size > 1024
THEN CAST(index_size / 1024 AS VARCHAR(10)) + 'Mb'
 ELSE CAST(index_size AS VARCHAR(10)) + 'KB'
 END AS index_size ,
 CASE WHEN unused > 1024 THEN CAST(unused / 1024 AS VARCHAR(10)) + 'Mb'
 ELSE CAST(unused AS VARCHAR(10)) + 'KB'
 END AS unused
FROM dbo.tablespaceinfo
ORDER BY reserved DESC

The running result is as shown in the figure:

At the same time, his third method returns too much data, a lot of which we don’t care much about. Yes, the original SQL statement is as follows:

Copy code The code is as follows:

View Code

SELECT OBJECT_NAME(id) tablename ,
* reserved / 1024 reserved ,
RTRIM(8 * dpages / 1024) + 'Mb' used ,
* ( reserved - dpages ) / 1024 unused ,
* dpages / 1024 - rows / 1024 * minlen / 1024 free ,
rows
FROM sysindexes
WHERE indid = 1
ORDER BY reserved DESC

The running result is as shown in the figure:

This contains some index information. In fact, we only care about the disk information occupied by the table. The modified SQL statement is as follows:

Copy code The code is as follows:

View Code 
SELECT OBJECT_NAME(id) tablename ,
 CASE WHEN reserved * 8 > ; 1024 THEN RTRIM(8 * reserved / 1024) + 'MB'
 ELSE RTRIM(reserved * 8) + 'KB'
 END DataReserve ,
 CASE WHEN dpages * 8 > 1024 THEN RTRIM( 8 * dpages / 1024) + 'MB'
 ELSE RTRIM(dpages * 8) + 'KB'
 END Used ,
 CASE WHEN 8 * ( reserved - dpages ) > 1024
 THEN RTRIM( 8 * ( reserved - dpages ) / 1024) + 'MB'
 ELSE RTRIM(8 * ( reserved - dpages )) + 'KB'
 END unused ,
 CASE WHEN ( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ) > 1024
              THEN RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024)                                         '
 ELSE RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ))
 + 'KB'
 END FREE ,
 rows AS Rows_Count
FROM sys.sysindexes
W HERE indid = 1
 AND status = 2066 -- status='18'
ORDER BY reserved DESC