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 |