The company worked by the author using the SQL Server database, and the large amount of data is handled every day. Documentation, the author does not understand the structure and data of many tables in the background database, and there is a lot of trouble to daily maintenance.
In the process of studying the background database, I need some information about the database, for example, I want to know how much disk space for each user table, and arrange it, let me know which tables are relatively large, more data is more Wait - I believe that this may also be a problem with a lot of database administrators, so I am determined to make a universal stored procedure. I added some changes to the system's stored procedure sp_spaceuses to suit my requirements. I hope this stored procedure can help everyone. The stored procedure is as follows:
IF exists (select name from sysobjects where name = 'spaceused' and type = 'p')
Drop Procedure Spaceused
Go
Create Procedure Spaceused
AS
Begin
Declare @ID int - the object id of @objname.
Declare @Type Character (2) - The Object Type.
Declare @Pages Int - Working Variable for Size Calc.
Declare @dbname sysname
Declare @dbsize dec (15,0)
Declare @logsize dec (15)
Declare @BYTESPAGE DEC (15,0)
Declare @PagesPermb Dec (15, 0)
Declare @objname nvarchar (776) - The object we want size.
Declare @UPDateusage Varchar (5) - param. for specifying That
Create Table # TEMP1
(
Name Varchar (200) null,
Number of rows CHAR (11) NULL,
Keep space varchar (15) NULL,
Data use space varchar (15) NULL,
Index usage space VARCHAR (15) NULL,
Unused space varchar (15) NULL
)
--Select @ objName = 'n_dep' - usage info. Should Be Updated.
SELECT @ updateusage = 'false'
/ * CREATE TEMP TABLES BEFORE ANY DML TO ENSURE DYNAMIC
** We need to create a Temp Table to do the calculation.
** Reserved: SUM (Reserved) Where Indid in (0, 1, 255)
** Data: SUM (DPAGES) Where Indid <2 SUM (Used) Where Indid = 255 (Text) ** Indexp: Sum (Used) Where Indid In (0, 1, 255) - DATA
** Unused: SUM (Reserved) - SUM (USED) Where Indid In (0, 1, 255)
* /
Declare Cur_Table Cursor for
Select Name from sysobjects where type = 'u'
Open cur_table
Fetch next from cur_table @Objname
While @@ fetch_status = 0
Begin
Create Table #SPT_SPACE
(
Rows Int Null,
Reserved dec (15) NULL,
Data Dec (15) NULL,
Indexp Dec (15) NULL,
Unused dec (15) NULL
)
/ *
** Check to See if User Wants Usages Updated.
* /
IF @updateusage is not null
Begin
Select @ updateusage = limited (@UPDATEUSAGE)
IF @UPDateusage Not in ('True', 'False')
Begin
Raiserror (15143, -1, -1, @ UpdateUSAGE)
Return (1)
end
end
/ *
** Check to see That The ObjName is LOCAL.
* /
IF @objname is not null
Begin
Select @dbname = parsion (@objname, 3)
IF @dbname is not null and @dbname <> db_name ()
Begin
Raiserror (15250, -1, -1)
Return (1)
end
IF @dbname is null
SELECT @dbname = db_name ()
/ *
** Try to find the object.
* /
SELECT @ID = NULL
SELECT @ID = ID, @Type = xipepe
From sysobjects
Where id = Object_ID (@objname)
/ *
** Does The Object EXIST?
* /
IF @id is null
Begin
Raiserror (15009, -1, -1, @ ObjName, @ dbname)
Return (1)
end
IF not exists (Select * from sysindexes
Where @ID = ID and INDID <2)
IF @Type in ('P', 'D', 'R', 'Tr', 'C', 'RF') - Data Stored In Sysprocedures
Begin
Raiserror (15234, -1, -1)
Return (1)
end
Else if @Type = 'v' - view => No Physical Data Storage.Begin
Raiserror (15235, -1, -1)
Return (1)
end
Else if @Type in ('pk', 'uq') - No Physical Data Storage. -?!?! Too Many Similar Messages
Begin
Raiserror (15064, -1, -1)
Return (1)
end
Else if @Type = 'f' - fk => No Physical Data Storage.
Begin
Raiserror (15275, -1, -1)
Return (1)
end
end
/ *
** Update Usages if User specified to do so.
* /
IF @UPDateUSAGE = 'True'
Begin
IF @objname is null
DBCC UpdateUsage (0) with no_infomsgs
Else
DBCC UpdateUsage (0, @ ObjName) with no_infomsgs
PRINT ''
end
Set nocount on
/ *
** if @ID is null, The WE WANT SUMMARY DATA.
* /
/ * Space Used Calculate in the Following Way
** @dbsize = Pages Used
** @BYTESPERPAGE = D.LOW (where d = master.dbo.spt_values) IS
** The # of bytes per page when d.type = 'e' and
** D.Number = 1.
** size = @dbsize * D.low / (1048576 (or 1 MB))
* /
IF @id is null
Begin
SELECT @dbsize = sum (Convert (Dec (15), Size))
From dbo.sysfiles
WHERE (status & 64 = 0)
SELECT @logsize = sum (Convert (Dec (15), SIZE))
From dbo.sysfiles
WHERE (status & 64 <> 0)
Select @BYTESPAGE = LOW
From master.dbo.spt_values
WHERE NUMBER = 1
And type = 'e'
SELECT @PagesPermb = 1048576 / @BYTESPERPAGE
SELECT DATABASE_NAME = DB_NAME (),
Database_size =
LTRIM (STR ((@ dbsize @logsize) / @ pagespermb, 15, 2) 'MB'),
'UNALLOCATED SPACE' =
LTRIM (STR ((@ dbsize)
(SELECT SUM (Convert (Dec (15), RESERVED)
From sysindexeswhere indid in (0, 1, 255)
)) / @ pagespermb, 15, 2) 'MB')
PRINT ''
/ *
** Now Calculate The Summary Data.
** Reserved: SUM (Reserved) Where Indid in (0, 1, 255)
* /
INSERT INTO #SPT_SPACE (RESERVED)
Select Sum (Convert (Dec (15), RESERVED)
From sysindexes
WHERE INDID IN (0, 1, 255)
/ *
** Data: SUM (DPAGES) Where Indid <2
** sum (used) where indid = 255 (text)
* /
SELECT @Pages = SUM (Convert (Dec (15), DPAGES))
From sysindexes
WHERE Indid <2
SELECT @Pages = @Pages Isnull (SUM (Convert (Dec (15), Used), 0)
From sysindexes
WHERE INDID = 255
Update #SPT_SPACE
SET DATA = @Pages
/ * INDEX: SUM (USED) Where Indid in (0, 1, 255) - DATA * /
Update #SPT_SPACE
Set indexp = (SELECT SUM (Convert ")
From sysindexes
WHERE INDID IN (0, 1, 255)))
- Data
/ * unused: sum (reserved) - SUM (USED) Where Indid in (0, 1, 255) * /
Update #SPT_SPACE
Set unused = reserved
- (Select SUM (Convert))
From sysindexes
WHERE INDID IN (0, 1, 255)))
SELECT RESERVED = LTRIM (STR (Reserved * D.LOW / 1024., 15, 0)
'' 'KB'),
Data = LTRIM (STR (Data * D.LOW / 1024., 15, 0)
'' 'KB'),
Index_size = LTRIM (STR (INDEXP * D.LOW / 1024., 15, 0)
'' 'KB'),
Unused = LTRIM (STR (unused * d.low / 1024., 15, 0)
'' 'KB')
From #SPT_SPACE, MASTER.DBO.SPT_VALUES D
Where d.number = 1
And D.Type = 'e'
end
/ *
** We Want a Particular Object.
* /
Else
Begin
/ *
** Now Calculate The Summary Data.
** Reserved: SUM (Reserved) Where Indid in (0, 1, 255)
* /
INSERT INTO #SPT_SPACE (RESERVED)
Select SUM (Reserved) from SysIndexes
WHERE INDID IN (0, 1, 255)
And id = @ID
/ *
** Data: SUM (DPAGES) Where Indid <2
** sum (used) where indid = 255 (text)
* /
SELECT @Pages = SUM (DPAGES)
From sysindexes
WHERE Indid <2
And id = @ID
SELECT @Pages = @Pages Isnull (SUM (Used), 0)
From sysindexes
WHERE INDID = 255
And id = @ID
Update #SPT_SPACE
SET DATA = @Pages
/ * INDEX: SUM (USED) Where Indid in (0, 1, 255) - DATA * /
Update #SPT_SPACE
Set indexp = (SELECT SUM (USED)
From sysindexes
WHERE INDID IN (0, 1, 255)
And id = @ID)
- Data
/ * unused: sum (reserved) - SUM (USED) Where Indid in (0, 1, 255) * /
Update #SPT_SPACE
Set unused = reserved
- (Select Sum (Used)
From sysindexes
WHERE INDID IN (0, 1, 255)
And id = @ID)
Update #SPT_SPACE
Set rows = i.ROWS
From sysindexes i
WHERE I.Indid <2
And i.id = @ID
INSERT INTO # TEMP1
Select name = Object_name (@ID),
Rows = Convert (Char (11), ROWS),
Reserved = LTRIM (STR (Reserved * D.LOW / 1024., 15, 0)
'' 'KB'),
Data = LTRIM (STR (Data * D.LOW / 1024., 15, 0)
'' 'KB'),
Index_size = LTRIM (STR (INDEXP * D.LOW / 1024., 15, 0)
'' 'KB'),
Unused = LTRIM (STR (unused * d.low / 1024., 15, 0)
'' 'KB')
From #SPT_SPACE, MASTER.DBO.SPT_VALUES D
Where d.number = 1
And D.Type = 'e'
DROP TABLE #SPT_SPACE
end
Fetch next from cur_table @Objname
end
Close Cur_Table
Deallocate Cur_Table
Select * from # Temp1 Order by Len (Reserved Space) DESC, Reserved Space DESC
Drop Table # temp1
Return (0)
end
The principle is very simple, I believe everyone can understand, sp_spaceused is almost indiscriminately, and the call is also very simple. Direct execution can be executed, without any parameters, the stored procedure is executed, will put all the data sheets in the currently connected database according to all data sheets From large to small, there are other related information. If you can have a reference value for everyone, please give it to the Forgot2000 applause, thank you! This stored procedure passes in SQLServer7.0 / 2000.
