SQL Server 2000 Undocumented Stored Procedures


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

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

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 |