Home > Articles > Transact SQL Programming Articles

SQL Server Help: Useful System Stored Procedures

SQL Server provides a number of useful system stored procedures that allow you to determine information about the database itself. Note that if you have problems running any of these stored procedures, check that the user you're running them as actually has permissions to execute the stored procedure in question.

Find out which tables and views are in a database

This is simply achieved by making use of the sp_tables system stored procedure, i.e.

EXEC sp_tables

The returned results set contains a list of the tables (and views) in the current database. The TABLE_NAME column gives the name of the table. The TABLE_TYPE column indicates if the table is a TABLE, SYSTEM TABLE or a VIEW. The TABLE_OWNER column is also useful as it shows the table's owner.

Find out which columns are in a database table

This is achieved by making use of the sp_columns system stored procedure, passing in the table name as the parameter, i.e.

EXEC sp_columns 'sales'

The returned results set contains a list of the table's columns. There are quite a few columns in the results set, so only the most useful will be described here:

• The COLUMN_NAME column gives the name of the column.
• The TYPE_NAME column gives the column's data type.
• The LENGTH column gives the column's data type length.
• The IS_NULLABLE column shows whether the column accepts null values.

Programmatically display a View's Transact-SQL

The system stored procedure sp_helptext will return a results set containing the lines of Transact-SQL that comprise the View. For example, the following will return the text of the stored procedure Order Details Extended's CREATE VIEW statement:

EXEC sp_helptext 'Order Details Extended'

The text of the CREATE VIEW statement is contained within the Text column of the results set.

Find out which stored procedures are in a database

This is achieved by making use of the sp_stored_procedures system stored procedure,

EXEC sp_stored_procedures

The returned results set contains a list of the stored procedures in the database in which it is executed. in the results set, the PROCEDURE_NAME column gives the name of the stored procedure.

Programmatically display a stored procedure's Transact-SQL

The system stored procedure sp_helptext will return a results set containing the lines of Transact-SQL that comprise the stored procedure. For example, the following will return the text of the stored procedure CustOrdersDetail's CREATE PROCEDURE statement:

EXEC sp_helptext 'CustOrdersDetail'

The text is contained within the Text column of the results set.

Useful Links

• The SQL Documentation Tool for SQL Server automatically builds technical documentation for Microsoft SQL Server databases, saving you significant time and money. A trial version is available for download.

• The VB Documentation Tool can automatically document your Visual Basic 6.0 applications and associated SQL Server/Microsoft Access databases. A .NET Documentation Tool for the .NET Framework is also available.

Useful Development Tools

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers, Table Relationships and Dependencies.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

ASP Documentation Tool™

Automatically creates developer documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework Windows and ASP.NET applications written in C# or VB.NET and SQL Server 7/2000/2005 or Microsoft Access databases associated with the application. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

Indexing Service Companion

The Indexing Service Companion is a Windows application that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Sharepoint Portal Search Services.

Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

PHP Documentation Tool™

Automatically creates developer documentation for PHP web applications. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1.0Mb ZIP file).