List Stored procedures

[Erics44]

hI
Does anyone know how i can list the names of my stored procedures in a table/view? I know how to use the information schema for the tables and views.
Thanks in advance

 

[SQLDenis]

--Procedures
SELECT ROUTINE_NAME,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'


--functions
SELECT ROUTINE_NAME,*
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Erics44]

Thanks
Having tried it out it works exactly how I need, do you know how I would list the input and return variables for each procedure/function?

 

[SQLDenis]

yep, that stuff is in the INFORMATION_SCHEMA.ROUTINE_COLUMNS view

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

actually you need this: INFORMATION_SCHEMA.PARAMETERS,

INFORMATION_SCHEMA.ROUTINE_COLUMNS is for table valued functions


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[MastermindSQL]

SELECT
p.[name] ProcName,
c.[name] InputParam,
t.[name] DataType,
t.length Length
FROM sysobjects p
JOIN syscolumns c ON
p.[id] = c.[id]
JOIN systypes t ON
c.xtype = t.xtype
WHERE p.xtype = 'p'
AND p.[name] NOT LIKE 'dt_%'
AND t.[name] NOT LIKE 'sysname'

/*** will not work in SQL 2005 ***/

 

[MastermindSQL]

Sorry, it works in SQL 2005 also. I thought Microsoft has changed the system table schema in 2005. Anyway it is good.

 

[Erics44]

Thanks all