Stored procedure: List SQL Server database objects by selected type(s)

Stored procedure: List SQL Server database objects by selected type(s)

This is the first in a series of tips that present SQL code for a variety of utility stored procedures. These stored procedures are intended primarily for administrative purposes, and they are designed to be installed as system stored procedures. A system stored procedure is created in the master database with a prefix of "sp_". A system stored procedure is invoked like any other, but the "sp_" prefix tells SQL Server to look for the routine in the master database before looking elsewhere. Therefore, do not use the "sp_" prefix on a typical application stored procedure. A system stored procedure executes in the context of the current database even though it exists in the master database. This behavior makes system stored procedures great for implementing generic solutions to common tasks.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in May 2005

It's helpful to use routines like these in any database.

I will start this series of tips by presenting a very simple stored procedure.

The SQL code in Listing 1 creates a system stored procedure named sp_ListObjects. The routine returns a list of all the current SQL Server database objects of the types selected. The list includes object types, object names, parent object names (where applicable) and creation dates (where available).

The sp_ListObjects stored procedure references SQL Server system tables. You may find it's prudent to reject this approach for production application routines, but it should be fine for administrative work.

The sp_ListObjects stored procedure accepts three parameters. All three are optional. The first parameter is a list of specific object types to be included. The second parameter is a list of specific object types to be excluded. The third parameter selects between two default sets of object types to be included.

The first and second parameters are lists of object types separated by pipes (vertical bars). The effects of the parameters are combined with an AND operator, so typically only one of them would be used for a given call.

The third parameter takes effect only when the first two parameters are not specified (NULL). In that case, a zero (0) returns code objects and a one (1) returns structure objects. Code objects are defined as types V|P|FN|IF|TF|TR because such objects typically consist of SQL code. Structure objects are defined as types U|K|F|C|D|I because such objects define the structure of a database.

The object types are:

V - view
P - stored procedure
FN - user-defined function, scalar
IF - user-defined function, table-valued, in-line
TF - user-defined function, table-valued, multi-statement
TR - trigger

U - user table
K - constraint, primary key (or unique constraint)
F - constraint, foreign key
C - constraint, check
D - constraint, default
I - index

Refer to Books Online for more information about the different types of objects in a SQL Server database.

This example lists the structure objects from the Northwind database:

USE Northwind EXECUTE sp_ListObjects NULL,NULL,1

This example lists the views and stored procedures from the Northwind database:

USE Northwind EXECUTE sp_ListObjects 'V|P'

I hope you find this system stored procedure to be useful.

Click for the stored procedure: sp_ListObjects

About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has over 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Brian is a software developer, database developer, database administrator, and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top