Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Category Archives: Stored Procedure

Simple information about indexes can be found by running

sp_helpindex 'HumanResources.Employee'

where ‘HumanResources.Employee’ is the name of a table. index_name, index_description and index_keys are displayed in the results window:-

sp_helpindex

Tags: , , ,

Following on from my previous post on sp_help, the sp_helpdb stored procedure provides useful information at the database level.

Running sp_helpdb without specifying a database provides an overview of all databases within the instance.

sp_helpdb_resultsThe query returns the following information:-

name
database size
owner
database id
creation date
database status
compatibility level

By specifying a database,

sp_helpdb 'AdventureWorks' 

additional information is provided on that databases data files and log files:-

sp_helpdb

Tags: , , , , ,

sp_help is an invaluable inbuilt SQL Server procedure that returns information on a number of different objects. Here are some examples of its use:-

Executing

sp_help

without specifying a context returns a list of all objects within the current database. The name, owner and object type are returned. Additionally, any user defined data types within the database are listed:-

sp_help

Executing

sp_help tablename

returns information relating to the table. The first results set provides information relating to the table itself. The second results set provides information on the columns, such as data type and data length. Beneath that are information on identity columns, GUIDs, filegroup location, indexes, constraints and foreign key references:-

sp_help_table

Try running sp_help against other objects (indexes, columns, filegroups, constraints, procedures, functions) to see what useful information you can gather.

Tags: , , , ,

%d bloggers like this: