The most recent project needs to see the size of the data user table, including the number of record bars and the amount of disk space occupied. I've been searching the web for a long time. It is also possible to view the size of the space occupied by each table in the MSSQL database.
But its 2, 3 in the method returned more data, some are we do not care about the data, I AdventureWorks2012 data on the test. The second method code is as follows:
Copy Code code 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--Creating a result store table
(Nameinfo varchar (50),
rowsinfo int, reserved varchar (20),
Datainfo varchar (20),
Index_size varchar (20),
Unused varchar (20))
Delete from Tablespaceinfo--empty 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 (LTrim (RTrim (reserved)), Len (LTrim (RTrim (Reserved))-2) as int) desc
operation effect as shown:
Obviously this return result is wrong. But it provides a way of thinking, Modified SQL statementAs follows:
Copy Code code as follows:
View Code
IF not EXISTS (SELECT *
From Sys.tables
WHERE name = ' Tablespaceinfo ')
BEGIN
CREATE TABLE Tablespaceinfo--Creating a result store table
(
TABLE_NAME VARCHAR (50),
Rows_count INT,
Reserved INT,
Datainfo INT,
Index_size INT,
Unused INT
)
End
DELETE from Tablespaceinfo
--Empty data table
CREATE TABLE #temp--Creating a result store 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 @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 Note: Display database information
--sp_spaceused @updateusage = ' TRUE '
--itlearner Note: Display 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) + ' Mb '
ELSE CAST (reserved as VARCHAR) + ' KB '
End as Data_and_index_reserved,
Case when Datainfo > 1024
THEN CAST (datainfo/1024 as VARCHAR) + ' Mb '
ELSE CAST (Datainfo as VARCHAR) + ' KB '
End as Used,
Case when index_size > 1024
THEN CAST (index_size/1024 as VARCHAR) + ' Mb '
ELSE CAST (index_size as VARCHAR) + ' KB '
End as Index_size,
Case when unused > 1024 THEN CAST (unused/1024 as VARCHAR) + ' Mb '
ELSE CAST (unused as VARCHAR) + ' KB '
End as unused
From Dbo.tablespaceinfo
ORDER by reserved DESC
run the results as shown in figure:
At the same time his third method returned too much data, many of which we do not care about, the original SQL statement is as follows:
Copy Code code 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
run the results as shown in figure:
Here the bread contains some index information, in fact, we only care about the table to occupy disk information,Modified SQL statementAs follows:
Copy Code code 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 results of the operation are as follows:
There are some wrong places to welcome everyone to shoot bricks!
