SQL Server 6.5 Useful Undocumented Stored Procedures


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
EXEC sp_MShelptype


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
EXEC sp_MStablespace ‘authors’

Here is the result from my machine:

Rows        DataSpaceUsed IndexSpaceUsed
———– ————- ————–
23          2             8


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
EXEC sp_MSindexspace ‘authors’

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)


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
EXEC sp_MStablerefs ‘titleauthor’

Here is the result from my machine:

candidate_table       candidate_key    referenced
——————— —————- ———-
dbo.authors           N/A              1
dbo.titles            N/A              1


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 (‘?’)”


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
EXEC sp_MSkilldb ‘pubs’

Published with the explicit written permission of the author. Copyright 2001.

Pages: 1 2


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |