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
Hello
how to find the rate of interest – using stored procedure concept in sql server.
how can i get only user defined procedure…
select specific_name from information_schema.routines
where routine_type='PROCEDURE'
use filter:
select * from sys.objects where type=’u’
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
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
Is it possible to insert data using Stored procedure in number of tables with different fields number of fields
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
how do I do this for SQL server 2000?
not possible
We can get all procedures in the follwoing way .
I tried its working …
Select * from all_objects where object_type=’PROCEDURE”;
It should be
Select * from sys.all_objects where type='p'
Also it will work only from version 2008
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’)
Can anyone tell me where stored procedure is stored in SQL Server 2005?
yes in sql server
:)) That was really funny
in programmablity
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.
Check the compatibility level of the database
It should be 90
all_objects should be sys.all_objects
Select * from sys.all_objects where type='p'
Hi Jack
First select database name master then you query execute.
Hi How we can view the stored procedure in query anlyzer
which was written previousely.
what is the sytax to review that procedure.
EXEC sp_helptext ‘procedure_name’
Is there a way to do this using SS2k?
How to Identify fregmentation of the table,meand how much table is fregmented.
Display all stored procedures in SQL 2000:
select * from sysobjects where xtype=’p’ order by name
SELECT * FROM sysobjects WHERE xtype = ‘P’
Hi,
Can we list down all the stored procedures which are encrypted.
Regards,
Manoj Gambhir
@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.
How do I get the database name that a stored procedure is located in if the SQL Server is 2000?
how to structure a table for view n sir one question i want to apply for dba traning for 6 months bt me heard about no training giving in dba is it true or not plz revert me me doing mca(5 sem)