Skip to content

Mr SQL's Blog

“Musings on Microsoft SQL Server”

Tag Archives: Stored Procedure

20/10/2010 Obtaining Database Information Using sp_helpdb

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

20/10/2010 Using the sp_help Stored Procedure

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