Using sp_help and Friends to Explore a Database

You inherit a database and need a quick tour. Using sp_help and its companion procedures gives you columns, indexes, module text, and space details.

Begin in the Right Database

System procedures use your current database context. Confirm DB_NAME before running a tour. The same object name can exist in another database or schema, so qualify the object when possible. A report from the wrong database can look perfectly plausible.

I start by using sp_help for a quick object overview. Without a name, it lists objects in the current database. With a table or view name, it returns columns and other metadata. The output is broad and convenient. It is not a replacement for every catalog view when you need a precise filter.

What are you trying to learn first: shape, indexes, code, or size? Pick the right procedure. Running every tool without a question produces a large pile of result grids.

SELECT DB_NAME() AS CurrentDatabase;
EXEC sys.sp_help;

Using sp_help for Table Shape

Pass a schema qualified table name to sp_help when you know it. The procedure can return column types, identities, constraints, and indexes in separate result sets. Read the result set labels. A single execution can produce more than one grid, which is useful at a console and awkward for automated parsing.

The procedure reads metadata visible to your account. If an expected object is missing, check database context and permissions before assuming it was dropped. I have seen a harmless context mistake start a very serious conversation.

For repeatable inventory, query sys.objects, sys.columns, and sys.types directly. Those views let you select the exact fields you need and join them to other metadata. sp_help is a quick tour guide, not a stable report schema.

Ask sp_helpindex About One Table

sp_helpindex lists indexes for a table and describes key columns. It is fast when you need a first look before reading a query plan. The output does not tell you whether the workload uses an index, how much maintenance it costs, or whether a filtered predicate fits a query.

Some newer index features are not fully described by this old convenience procedure. Check sys.indexes and sys.index_columns for a detailed definition. Then inspect actual plans or Query Store if performance is the reason for the question.

The script below picks one visible user table so it can run in an unfamiliar database. If the database has no user table, it returns a message instead of inventing one.

DECLARE @tableName nvarchar(517) =
(
    SELECT TOP (1)
        QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name)
    FROM sys.tables
    ORDER BY name
);
IF @tableName IS NULL
    PRINT N'No visible user table in this database.';
ELSE
    EXEC sys.sp_helpindex @tableName;

Read Source With sp_helptext

sp_helptext displays the definition of a view, procedure, function, trigger, or another supported module when its text is available. It can also show system procedure text. The output is split into text rows. For searching and automation, OBJECT_DEFINITION or sys.sql_modules is easier to work with.

Encrypted definitions and missing VIEW DEFINITION rights can block the result. A blank or denied response does not prove the module has no code. Check permissions and object type before drawing that conclusion.

I read a system procedure when I want to understand its behavior, but I do not edit a system procedure to fix my own query. The source is a lesson, not an invitation to rearrange SQL Server’s furniture.

EXEC sys.sp_helptext N'sys.sp_help';

Treat sp_spaceused as an Estimate

sp_spaceused reports size and allocation information for a database or object. It helps answer whether a table is large enough to change your maintenance plan. Read the output labels. Reserved space, data, index space, and unused space are different measures.

Do not report a figure from sp_spaceused as a timeless fact. It is a reading from this database at this time. When counts or allocation details look stale, consult the documented update usage option and test its cost before running it on a busy system.

Use the same table chosen for the index example to see how the procedure behaves. Record the query time and database name beside any number you share.

DECLARE @tableName nvarchar(517) =
(
    SELECT TOP (1)
        QUOTENAME(SCHEMA_NAME(schema_id)) + N'.' + QUOTENAME(name)
    FROM sys.tables
    ORDER BY name
);
IF @tableName IS NOT NULL
    EXEC sys.sp_spaceused @tableName;

Know When DMVs Beat Using sp_help

The help procedures describe what exists. DMVs can describe what is happening or what happened since a counter reset. For active requests, use sys.dm_exec_requests. For index usage, use sys.dm_db_index_usage_stats. For file I/O, use sys.dm_io_virtual_file_stats.

Do not confuse an index definition with index usage. sp_helpindex can tell you a key exists. It cannot tell you that a slow query selected it. An actual plan gives that answer for the query. A DMV gives broader activity with time and reset limits.

I switch tools as soon as the question changes from “what is built?” to “what is running?” That saves time and prevents a metadata result from becoming a performance diagnosis.

Keep Using sp_help as the Fast Tour in Your Kit

Write a small script library with the help calls and equivalent catalog queries. Include comments about database context and permissions. Test the scripts on the SQL Server versions you manage. A remembered command is useful; a checked command is better.

After the first tour, inspect dependencies and application behavior before making changes. A table can look simple while jobs and services depend on it. Use the quick procedures to orient yourself, then use focused queries for the change plan.

The old procedures remain handy because they answer common questions quickly. Use them for the first five minutes, then let the specific problem choose the next tool.

If you plan to automate the result, inspect the procedure output on the target release before writing a parser. Convenience procedures can return several result sets and formatted text fields. A catalog query with named columns is easier to store in a table. Keep the help call for an interactive look, then write a specific SELECT for the report you intend to run every night.

Related reading on this blog: sp_helpdb: Accidental Discovery and sp_HelpText for sp_HelpText: Puzzle.

sp_help is not a full investigation, it is the quickest way to find the right door.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.