View the space occupied by each table in the MSSQL database

This article provides a detailed analysis of the space occupied by each table in the MSSQL Database. for details, see the size of the user table in the latest project, this includes the number of records and the amount of disk space occupied. After searching for a long time on the Internet, you can view the space occupied by each table in the MSSQL database.
However, the methods 2 and 3 return a large amount of data, some of which are data that we don't care about. I did a test on the AdventureWorks2012 data. The code for the second method is as follows:

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 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
-- Itlearner note: displays database information
Sp_spaceused @ updateusage = 'true'
-- Itlearner note: displays table information
Select *
From tablespaceinfo
Order by cast (left (ltrim (rtrim (reserved), len (ltrim (rtrim (reserved)-2) as int) desc

Running effect

Obviously, this returned result is incorrect. However, it provides an idea, Modified SQL statementAs follows:

The code is as follows:

View Code
If not exists (SELECT *
FROM sys. tables
WHERE name = 'tablespaceinfo ')
BEGIN
Create table tablespaceinfo -- CREATE a result storage TABLE
(
Table_Name VARCHAR (50 ),
Rows_Count INT,
Reserved INT,
Datainfo INT,
Index_size INT,
Unused INT
)
END
Delete from tablespaceinfo
-- Clear a data table
Create table # temp -- CREATE a result storage TABLE
(
Nameinfo VARCHAR (50 ),
Rowsinfo INT,
Reserved VARCHAR (20 ),
Datainfo VARCHAR (20 ),
Index_size VARCHAR (20 ),
Unused VARCHAR (20)
)
DECLARE @ tablename VARCHAR (255)
-- Table name
DECLARE @ brief SQL 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 @ resolve SQL = 'Insert into # temp exec sp_spaceused ''' + @ tablename
+ ''''
EXECUTE sp_executesql @ explain SQL
Fetch next from Info_cursor
INTO @ tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
-- Itlearner note: displays database information
-- Sp_spaceused @ updateusage = 'true'
-- Itlearner note: displays table information
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

Running result

At the same time, the third method returns too much data, many of which are of little concern to us. The original SQL statement is as follows:

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

Running result

Here, we only care about the disk occupied by the table, Modified SQL statementAs follows:

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)
/1024) + 'mb'
Else rtrim (8 * dpages/1024-rows/1024 * minlen/1024 ))
+ 'KB'
End free,
Rows AS Rows_Count
FROM sys. sysindexes
WHERE indid = 1
AND status = 2066 -- status = '18'
Order by reserved DESC

The running result is as follows:

If something is wrong, you are welcome to make a picture!