SQL Server 6.5 Useful Undocumented Stored Procedures

In this article, I want to tell you about some useful undocumented stored procedures shipped with SQL Server 6.5.

sp_fixindex

This stored procedure can be used to fix a corruption in a system table by recreating the indexes on the system table. This is an example:

EXEC sp_fixindex pubs, sysindexes, 2

sp_lock2

As a SQL Server DBA, I often need information about locks. Microsoft recommends to use the sp_lock system stored procedure to report locks information. This is a very useful procedure that returns useful information about the SQL Server process ID, the locktype, the table, and database name. This is the result set of sp_lock stored procedure:


spid   locktype     table_id    page       dbname
—— ———— ———– ———- ——-
12     Sh_intent    688005482   0          master
12     Ex_extent    0           336        tempdb


Microsoft also provides an enhanced version of the sp_lock system stored procedure, which returns TableName and Owner also. This is sp_lock2 stored procedure. This is an example:

EXEC sp_lock2



sp_who2

This stored procedure returns information about current SQL Server 6.5 users and processes, similar to sp_who, but it provides more detailed information. It returns CPUTime, DiskIO, LastBatch and ProgramName, in addition to the information provided by sp_who.

The syntax is:

sp_who [login_id | 'spid']

where:

login_id is the user’s login ID. If the login_id is not specified, the procedure reports on all of the active users of SQL Server

spid – the specific process id

This example returns information for the ‘sa’ login:

EXEC sp_who2 ‘sa’

sp_tempdbspace

This stored procedure can be used to find the total size and the space used by the tempdb database. You should execute sp_tempdbspace without parameters. This is an example:

EXEC sp_tempdbspace

Here is the result set from my machine:

database_name   database_size   spaceused
tempdb          2.000000        0.640625

sp_MShelpcolumns

This stored procedure returns the complete schema for a table, including the length, type, name, and whether a column is computed. You should specify the @tablename parameter to work with sp_MShelpcolumns. To return the full column description for the authors table in the pubs database, run:

USE pubs
GO
EXEC sp_MShelpcolumns ‘authors’
GO

sp_MShelpindex

This stored procedure returns information about name, status, fill factor, index columns names, and about used segments for the given table. You should specify the @tablename parameter to work with sp_MShelpindex. To return the indexes description for the authors table in the pubs database, run:

USE pubs
GO
EXEC sp_MShelpindex ‘authors’
GO

Continues…

Pages: 1 2




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 |