This tip starts what could be a very long series of tips that present the 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, the "sp_" prefix should not be used 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. Such routines are helpful to use with any database. I will start the series 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 objects of selected types in the current database. 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. It may be prudent to avoid the practice 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 contraint) F - constraint, foreign key C - constraint, check D - constraint, default I - index Refer to BOL 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.