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