-- The size of the ms SQL Server case that makes it easy to obtain the data table
-- The following method can only be used for estimation, but it can roughly solve some ways for the resource manager to check the growth of the data.
Declare @ Tblname Varchar ( 50 ) -- Data Table name changes (user data table name changes)
If Exists ( Select * From DBO. sysobjects Where ID = Object_id (N ' [DBO]. [# tablestatics_temp] ' ) And Objectproperty (ID, n ' Isusertable ' ) = 1 )
Drop Table [ DBO ] . [ # Tablestatics_temp ] -- Inventory data table
Create Table # Tablestatics_temp ( -- Create a metadata table
[ Name ] [ Varchar ] ( 50 ) Collate chinese_taiwan_stroke_ci_as Null , -- Data Table Name ‑
[ Rows ] [ Varchar ] ( 50 ) Collate chinese_taiwan_stroke_ci_as Null , -- Current data columns in the data table
[ Reserved ] [ Varchar ] ( 50 ) Collate chinese_taiwan_stroke_ci_as Null , -- The size of the Space configured by the object in the Resource
[ Data ] [ Varchar ] ( 50 ) Collate chinese_taiwan_stroke_ci_as Null , -- Data Space size
[ Index_size ] [ Varchar ] ( 50 ) Collate chinese_taiwan_stroke_ci_as Null , -- Size of the blank space used by the index
[ Unused ] [ Varchar ] ( 50 ) Collate chinese_taiwan_stroke_ci_as Null ) -- The size of the space reserved for objects in the resource but not used
Declare Cur_tablestatics Cursor Forward_only For
Select Name From Sysobjects Where Xtype = ' U ' Order By Name -- Retrieve the user data table name
Open Cur_tablestatics
Fetch Next From Cur_tablestatics
Into @ Tblname
While @ Fetch_status = 0
Begin
Insert # Tablestatics_temp
Exec Sp_spaceused @ Tblname , @ Updateusage = N ' True ' -- Displays the related disk space information of the data table.
Fetch Next From Cur_tablestatics
Into @ Tblname
End
Close Cur_tablestatics
Deallocate Cur_tablestatics
Select Name, rows,
Convert ( Varchar ( 18 ), Convert (Numeric ( 18 , 2 ), Substring (Reserved, 1 , Len (Reserved) - 2 )) / 1024 ) + ' MB ' As Reserved,
Convert ( Varchar ( 18 ), Convert (Numeric ( 18 , 2 ), Substring (Data, 1 , Len (Data) - 2 )) / 1024 ) + ' MB ' As Data,
Convert ( Varchar ( 18 ), Convert (Numeric ( 18 , 2 ), Substring (Index_size, 1 , Len (Index_size) - 2 )) / 1024 ) + ' MB ' As Index_siz,
Convert ( Varchar ( 18 ), Convert (Numeric ( 18 , 2 ), Substring (Unused, 1 , Len (Unused) - 2 )) / 1024 ) + ' MB ' As Unused
From # Tablestatics_temp Where Rows <> ' 0 ' -- Query the data table with the ratio of data saved in the metadata table being different.
Drop Table # Tablestatics_temp -- Remove from the inventory table
