sp_MShelptype
This stored procedure returns much useful information about system data types and user data types.
Syntax
sp_MShelptype [typename] [, flags] |
where
typename – is the type name. typename is nvarchar(517), with a default of NULL.
flags – flags is nvarchar(10), with a default of NULL
To get information about all built-in and user defined data types in the pubs database, run:
USE pubs
GO EXEC sp_MShelptype GO |
sp_MSindexspace
This stored procedure returns the size in kb, of the indexes found in a particular table.
Syntax
sp_MSindexspace tablename [, index_name] |
where
tablename – is the table name, tablename is nvarchar(517)
index_name – is the index name, index_name is nvarchar(258), with a default of NULL
To determine the space used by the indexes from the authors table in the pubs database, run:
USE pubs
GO EXEC sp_MSindexspace ‘authors’ GO |
sp_MSkilldb
This stored procedure sets a database to suspect mode and uses DBCC DBREPAIR to kill it. You should run this sp from the context of the master database. Use it very carefully.
Syntax
sp_MSkilldb dbname |
where
dbname – is the database name, dbname is nvarchar(258)
To kill the pubs database, run:
USE master
GO EXEC sp_MSkilldb ‘pubs’ GO |
sp_MStablespace
This stored procedure returns the number of rows in a table and the space the table and index use.
Syntax
sp_MStablespace name [, id] |
where
name – is the table name, name is nvarchar(517)
id – id is int, with a default of NULL.
To determine the space used by the authors table in the pubs database, run:
USE pubs
GO EXEC sp_MStablespace ‘authors’ GO |
Here is the result set from my machine:
Rows DataSpaceUsed IndexSpaceUsed
———– ————- ————– 23 8 32 |