Recent projects need to look at the size of the data user table, including the number of record bars and the number of disk space occupied. On the internet for a long time it is possible to view the size of each table in the MSSQL database relative to the space consumed.
But its 2, 3 method returns more data, some of which we do not care about, and I do the test on the AdventureWorks2012 data. The second method code is as follows:
Copy CodeThe 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--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 (left (LTrim (RTrim (reserved)), Len (LTrim (RTrim (reserved)))-2 as int) desc
Run effect
It is clear that the return result is wrong. But it provides a way of thinking, the modified SQL statementAs follows:
Copy CodeThe code is 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
--Clear the data sheet
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 > 1024x768 then CAST (unused/1024 as VARCHAR) + ' Mb '
ELSE CAST (unused as VARCHAR) + ' KB '
END as unused
From Dbo.tablespaceinfo
ORDER by reserved DESC
Run Results
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 CodeThe code is as follows:
View Code
SELECT object_name (ID) tablename,
* reserved/1024 reserved,
RTRIM (8 * dpages/1024) + ' Mb ' used,
* (reserved-dpages)/1024x768 unused,
* dpages/1024-rows/1024 * minlen/1024 free,
Rows
From sysindexes
WHERE indid = 1
ORDER by reserved DESC
Run Results
Here the bread contains some index information, in fact, we only care about the table occupies disk information,the modified SQL statementAs follows:
Copy CodeThe code is as follows:
View Code
SELECT object_name (ID) tablename,
Case when reserved * 8 > 1024x768 then RTRIM (8 * reserved/1024) + ' MB '
ELSE RTRIM (Reserved * 8) + ' KB '
END Datareserve,
Case when Dpages * 8 > 1024x768 then RTRIM (8 * dpages/1024) + ' MB '
ELSE RTRIM (Dpages * 8) + ' KB '
END used,
Case 8 * (reserved-dpages) > 1024
Then RTRIM (8 * (reserved-dpages)/1024x768) + ' 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)
/1024x768) + ' 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 is no place to welcome everyone to shoot bricks!
http://www.bkjia.com/PHPjc/327781.html www.bkjia.com true http://www.bkjia.com/PHPjc/327781.html techarticle recent projects need to look at the size of the data user table, including the number of record bars and the number of disk space occupied. Looking for a long time on the internet where to view MSSQL database each table occupies space size ...
