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 (https://blog.sqlauthority.com)
SQL SERVER – 2005 – List All Stored Procedure in Database
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
66 Comments. Leave new
Hi,
How to extract an encrypted stored procedure to DDL or other binary file? And this file can be used to run on SQL Query or other tools to copy procedures only to other database without moving other objects.
Thanks,
Xiaogang
Hi,
Try this on SQL Prompt, u can view all the Procedures
SELECT OBJECT_NAME, OBJECT_TYPE FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE=’PROCEDURE’;
PROGRAMMATICALLY – how do you list ALL of the parameters for a given stored procedure?
@Rob
For A quick look,
use mydb
Exec Sp_help [ownername.spname]
Above script gives you details of procedure including the parameters information for that stored procedure.
~ IM.
Hi Imran Mohammed,
Can you tell me how to list ALL of the parameters for ALL stored procedures simultaneously in a single query ?? It would make learning stored procedures of SQL Server 2005 very easy. Is there any keyboard shortcut for this ??
Thanks & Regards,
Pratik.
Please use :
SELECT *
FROM sys.all_objects where type=’p’ order by name
Thanks it was helpful
How to get list of input parameters of any stored proc which listed by querying sys.objects or dbo.sysobjects in 2005 ???
Guys we got da solution for same:
is like below:
SELECT Name,PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE,Character_Maximum_Length
FROM INFORMATION_SCHEMA.PARAMETERS A
INNER JOIN
Sys.objects B ON
A.SPECIFIC_Name = B.Name
WHERE B.Name LIKE ‘SprocName%’ AND TYPE =’P’
hi friend,
i posted the following question in ur all forum,
i have a bulk XML database, i want this database to be import in sql server 2005 table using CREATE PROCEDURE, pls help out this soluition.
Can anyone please tell me, how do I ge the listing of the all the Stored Procs which are updating records or deleting records in/of a give table?
This is very old style.
Please give some thing new code
try this:
select o.name as sp_name,
(len(c.text) – len(replace(c.text, char(10), ”))) as lines_of_code,
case when o.xtype = ‘P’ then ‘Stored Procedure’
when o.xtype in (‘FN’, ‘IF’, ‘TF’) then ‘Function’
end as type_desc
from sysobjects o
inner join syscomments c
on c.id = o.id
where o.xtype in (‘P’, ‘FN’, ‘IF’, ‘TF’)
and o.category = 0
and o.name not in (‘fn_diagramobjects’, ‘sp_alterdiagram’, ‘sp_creatediagram’, ‘sp_dropdiagram’, ‘sp_helpdiagramdefinition’, ‘sp_helpdiagrams’, ‘sp_renamediagram’, ‘sp_upgraddiagrams’, ‘sysdiagrams’)
Hi,
I need to get the stored procedures from two different database. Is that possible? If so, help me.
I was using this query to retrieve stored procedures from the SQL Server. But it retrieves ONLY from the currently mounted database.
select specific_name,specific_catalog,created, last_altered from information_schema.routines
where routine_type=’PROCEDURE’
order by created, last_altered
What I need is,
(i.e) Consider Database1 and Database2
specific_namespecific_catalog,created, last_altered
Thanks,
Sabarish
Hi,
I need to get the stored procedures from two different database. Is that possible? If so, help me.
I was using this query to retrieve stored procedures from the SQL Server. But it retrieves ONLY from the currently mounted database.
select specific_name,specific_catalog,created, last_altered from information_schema.routines
where routine_type=’PROCEDURE’
order by created, last_altered
What I need is,
(i.e) Consider Database1 and Database2
=========================================
specific_name specific_catalog created last_altered
=========================================
Sp_getname Database1 2009-01-09 2009-01-09
Sp_getage Database2 2009-01-09 2009-01-09
=========================================
Thanks,
Sabarish
Would it possible to create a query that would extract the code of each stored procedure into distinct files?
The above Query is not working in list the all procedures in Database
Simple and exec sp_stored_procedures
Hi Pinal / All,
is there any way to restore the deleted stored procedure in SQL 2005 ?
Thanks,
Yash
I want to display all the system stored procedures name with input/output parameters name along with their datatypes.
Select * from sys.all_objects where type=’p’ doesn’t give information about input/output parameter names.
exec sp_stored_procedures displays information about i/o parameters name but what is PROCEDURE_TYPE.
What is the name of main tab;e and where can I find that..
Same thing I want to know for System Functions.
select parameter_name from information_schema.parameters where specific_name = ‘SP Name’