Get a list of all user defined stored procedures, functions, etc

If you are using SQL Server 2005 and newer you can use the queries below to get a list of any user defined object (including, but not limited to stored procedures, scalar-valued functions, table-valued functions, aggregate functions, and views) for a given database on a SQL Server 2005 installation. For a complete list of objects that you can list click here. The queries below will give you the same results you get when you look in Object Explorer in SSMS (Microsoft SQL Server Managements Studio).

All User Defined Objects

This will give you all the objects listed here.

-- all user defined objects
SELECT  sys.schemas.name + '.' + sys.objects.name, type, type_desc
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
order by 3,1

Common User Defined Objects

This will give you a list of the following user defined objects: stored procedures, views, table-values functions, scalar-valued functions, aggregate functions.

-- common user defined objects
SELECT  sys.schemas.name + '.' + sys.objects.name, type, type_desc
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in
(
'p', 'pc', -- stored procs
'v', -- views
'tf', 'if', 'ft', -- table-valued functions
'fn', 'fs', -- scalar-valued functions
'af' -- aggregate functions
)
order by 3,1

User Defined Stored Procedures

-- user defined stored procs
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('p', 'pc')
order by 1

User Defined Views

-- user defined views
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type='v'
order by 1

User Defined Table-Valued Functions

-- user defined table-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('tf', 'if', 'ft')
order by 1

User Defined Scalar-Valued Functions

-- user defined scalar-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('fn', 'fs')
order by 1

User Defined Aggregate-Valued Functions

-- user defined aggregate-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type = 'af'
order by 1