SQL SERVER – 2005 – List All Stored Procedure in Database « Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 – List All Stored Procedure in Database

November 27, 2007 by pinaldave

Run following simple script on SQL Server 2005 to retrieve all stored procedure in database.
SELECT * FROM sys.procedures;

This will ONLY work with SQL Server 2005.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 18 Comments

18 Responses

on March 14, 2008 at 5:18 pm Preethi

Hello

how to find the rate of interest – using stored procedure concept in sql server.
on March 22, 2008 at 5:49 pm SAVITHA

how can i get only user defined procedure…
on April 1, 2008 at 1:39 pm AgnesLoyola

Hi Savitha

You can use use the below query to list all User Defined Stored procedures in a database:

Select * from sys.procedures where [type] = ‘P’ and is_ms_shipped = 0 and [name] not like ’sp[_]%diagram%’

OR

Select * from sys.objects where type=’p’ and is_ms_shipped=0 and [name] not like ’sp[_]%diagram%’

‘NOT LIKE’ part is added to get rid of stored procedures created during database installation.

i have taken this from http://vadivel.blogspot.com/2006/10/listing-dropping-all-stored-procedures.html
on April 2, 2008 at 2:09 pm Sumon

We can find the Stored Procedures of a data base like this:

select * from dbo.sysobjects where xtype=’p’ and OBJECTPROPERTY(id, N’IsProcedure’) = 1
on June 17, 2008 at 12:25 pm tushar

Is it possible to insert data using Stored procedure in number of tables with different fields number of fields
on June 20, 2008 at 8:45 am Amol Kagde

hi tushar,

Yes this is possible using one stored procedure , that insert different data fields in diffrent tables.

Create Proc name
(
Declare here all fields
)
as
Write here different insert query statement for differrent tables simultaneously it will fire and exe.

ok bye
on July 8, 2008 at 10:26 pm eruntale

how do I do this for SQL server 2000?
on August 30, 2008 at 3:59 pm Hanuman

We can get all procedures in the follwoing way .
I tried its working …

Select * from all_objects where object_type=’PROCEDURE”;
on September 3, 2008 at 10:42 am Ben GU

in sql 2005

SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published,
is_schema_published
FROM sys.all_objects
WHERE (is_ms_shipped = 0) AND (type_desc = N’SQL_STORED_PROCEDURE’)
on September 10, 2008 at 11:46 am Amit

Can anyone tell me where stored procedure is stored in SQL Server 2005?
on September 12, 2008 at 3:46 am Jack

you guys are all crazy:
(from SQL Sever 2005 Management Studio)

SELECT * FROM sys.procedures
Msg 208, Level 16, State 1, Line 1
Invalid object name ’sys.procedures’.

Select * from all_objects where object_type=’PROCEDURE’
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘all_objects’.

Probably because it’s a SQL Server 2000 database.
on September 18, 2008 at 1:47 am atul

Hi How we can view the stored procedure in query anlyzer
which was written previousely.
what is the sytax to review that procedure.
on September 18, 2008 at 9:27 pm Carol

Is there a way to do this using SS2k?
on January 16, 2009 at 6:23 am Priyanka

How to Identify fregmentation of the table,meand how much table is fregmented.
on March 30, 2009 at 11:17 am Srinivasan Kumar

Display all stored procedures in SQL 2000:

select * from sysobjects where xtype=’p’ order by name
on June 9, 2009 at 8:55 pm Patricio Carpio

SELECT * FROM sysobjects WHERE xtype = ‘P’
on July 9, 2009 at 4:54 pm Manoj Gambhir

Hi,
Can we list down all the stored procedures which are encrypted.

Regards,
Manoj Gambhir
on July 10, 2009 at 9:26 am Imran Mohammed

@Manoj,

Select SO.Name [Stored Procedure Name]
from sysobjects SO
join Syscomments SC on SO.id = SC.id
where SO.type = ‘P’
AND SC.encrypted = 1

Ex: Create a procedure with encryption and check,

Create Procedure USP_Ex1
with encryption
as
select * from sysobjects

go

Select SO.Name [Stored Procedure Name]
from sysobjects SO
join Syscomments SC on SO.id = SC.id
where SO.type = ‘P’
AND SC.encrypted = 1

go

drop procedure USP_Ex1

~ IM.