Back To Main Page

Free Downloads

Learn VB - Free Guide

Knowledgebase Articles

SQL Hints and Tips

Undocumented SQL Commands

SQL Server 2000 and SQL Server 2005 have a variety of undocumented stored procedures which can greatly make your development and working with SQL Server much easier.

Below are a list of some of the undocumented stored procedures, useful documented stored procedures and useful bits of code that we have used in the past. They are aimed at medium to advanced SQL developers/DBAs so if you are a beginner you might get a bit lost.

sp_msForEachDB To do something with all databases on a server.
sp_msForEachTable To do something with all tables in a database.
sp_msHelpColumns Lists all fields and attributes of a table.
sp_msHelpType Detailed information about the system and user-defiend datatypes
sp_msIndexSpace Tells you the sizes of the indexes on a table
sp_msTableSpace Tells you the rows and spaced used by a table
sp_TempDBSpace Tells you the size of the tempDB
sp_msHelpIndex Detail about the indexes in a table
sp_helptext Shows you the TSQL inside an SP
sp_who2 Improved version of sp_who - tells you whos connected to the DB
 

Sp_msForEachDB

This SP gets back a list of databases on your server and allows you to run a command/action against each one.

Example:

EXEC Sp_msForEachDB @Command1 = "DBCC CHECKDB (?)"

Sp_msForEachTable

This is similar to the sp_msForEachDB stored procedure, but it works against tables in a databases rather than databases on your server.

Example:

Use NORTHWIND
GO
EXEC Sp_msForEachTable @Command1 = "sp_spaceused '?'"
Potential uses could be to each day take a snapshot of each table's size so you can look back and see how things are growing, OR, rebuild the indexes in all tables each evening.

Sp_msHelpColumns

This stored procedure lists all the fields and attributes of each field in a table, eg. Whether its full text indexed, data type, field length etc.

Syntax:
Sp_msHelpColumns tablename [, flags] [,orderby] [,flags2] Tablename (varchar) is the name of a table; Flags (int), default is zero; Orderby (varchar) is the return sequence; Flags2 (int), default is zero; Example:

USE NorthWind
GO
EXEC Sp_msHelpColumns Customers

Sp_msHelptype

Following on from the sp_msHelpColumns stored procedure, sometimes you will want to know more about some of the datatypes - both system and user-defined. This SP lists all the datatypes, or just a specific one:

Syntax:
Sp_msHelptype [typename] Example:

EXEC sp_msHelptype

Sp_msIndexSpace

This stored procedure returns the index names and sizes of all indexes on a table. Returned space sizes are in KB.

Syntax:
Sp_ msIndexSpace {Name} Example:

USE NorthWind
GO
EXEC Sp_msIndexSpace Customers

Sp_msTableSpace

This stored procedure returns the number of rows and the amount of data space and index spaced used by the table. Returned space sizes are in KB.

Syntax:
Sp_msTableSpace {Name} Example:

USE NorthWind
GO
EXEC Sp_msTableSpace Customers

Sp_TempDBSpace

A quick way of finding out how big the tempdb database is. Figures returned are in MB.

Example:

EXEC sp_tempdbspace

Sp_msHelpIndex

This stored procedure gives you high detail about the indexes on a table. It lists all the different indexes (if any), the fields which are indexed on each index, fill factor, ascending/descending and a whole load of other information.

Syntax:
Sp_mshelpindex {tablename} Example:

USE NorthWind
GO
EXEC Sp_msHelpIndex Customers

Sp_helptext

One of the most useful SPs you'll come across. It basically returns the SQL inside a stored procedure - so you can see exactly what the code is doing - unless the stored procedure was CREATEd with a 'WITH ENCRYPTION' parameter. Example:
USE NorthWind
GO
EXEC sp_helptext CustOrderHist

Sp_Who2

The sp_who stored procedure is good for finding out whos currently logged into a database and what they are doing - but it is a little limited. This stored procedure returns a more comprehensive set of data including CPUTime, DiskIO, LastBatch, Command and more.

Example:

EXEC sp_who2