sp_MShelptype
This stored procedure returns much useful information about system and user data types. You can specify @typename parameter to work with sp_MShelpindex, or you can run sp_MShelpindex stored procedure without parameters. To return information about all built-in and user defined data types, run:
USE pubs
GO
EXEC sp_MShelptype
GO
sp_MStablespace
This stored procedure returns the number of rows and the amount of space the table and index use. You should specify @name parameter to work with sp_MShelpindex. To return the amount of space used by the authors table in the pubs database, run:
USE pubs
GO
EXEC sp_MStablespace ‘authors’
GO
Here is the result from my machine:
Rows DataSpaceUsed IndexSpaceUsed
———– ————- ————–
23 2 8
sp_MSindexspace
This stored procedure returns the size in kb, which the indexes in the particular table use.
The syntax is:
sp_MSindexspace [@tablename, [@index_name]]
where @tablename – the table name
@index_name – the index name
This is an example:
USE pubs
GO
EXEC sp_MSindexspace ‘authors’
GO
Here is the result from my machine:
Index ID Index Name Size (KB) Comments
——– —————————— ———– ——–
1 UPKCL_auidind 4 Size excludes actual data.
2 aunmind 4 (None)
sp_MStablerefs
This stored procedure, when used with the @tablename parameter, returns all the dependencies for any table. To get all dependencies for the titleauthor table in the pubs database, run:
USE pubs
GO
EXEC sp_MStablerefs ‘titleauthor’
GO
Here is the result from my machine:
candidate_table candidate_key referenced
——————— —————- ———-
dbo.authors N/A 1
dbo.titles N/A 1
sp_MSforeachtable
Sometimes, you need to perform the same actions for all tables in a database. You can create a cursor for this purpose, or you can also use sp_MSforeachtable stored procedure. For example, you can use this stored procedure to rebuild all indexes in your database. Try to schedule it to execute when your server is not very busy.
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”
sp_MSkilldb
This stored procedure sets a database to suspect mode and lets dbcc dbrepair kill it. You should run this sp from the context of the master database. Use it very carefully. To kill the pubs database, run:
USE master
GO
EXEC sp_MSkilldb ‘pubs’
GO
Published with the explicit written permission of the author. Copyright 2001.
]]>