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
)
Journey to SQL Authority with Pinal Dave
SQL, SQL Server, MySQL, Big Data and NoSQL
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 http://vadivel.blogspot.com/2006/10/listing-dropping-all-stored-procedures.html
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
It is possible my dear.. may be all are not knowledge about this.. but recently not any one can use sql server 2000……..
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)
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’
How can I find stored procedures that have never been executed. Not only in the time the log persists in memory? Any idea?
If is not posible I am trying to find out how to save the number of times the stored procedures of my database are executed. Any help please. It is driving me crazy!! Maybe there is a table where all this information is saved.
List Stored Procedures including plain script.
List Stored Procedures including plain script:
SELECT r.ROUTINE_NAME, r.Routine_Definition
FROM INFORMATION_SCHEMA.Routines r order by r.ROUTINE_NAME
thanks and it was very helpful and new to me other than sp_help
I need to copy a store procedures to txt file, by coding only
How to find DBName using ‘SPname’ in sql on which DB it exists.
SELECT * FROM SYSOBJECTS WHERE XTYPE IN (‘P’,'X’) AND category=0 ORDER BY NAME
HOW CAN I RESOLVE THIS
list the titles, prices and advances
of all the books with the
price greater than 20% of the advance
thanks
Hi,
I need to pull the all userdefined object info from one database.
User defined objects – Tables,index,constraints ,SP,Functions Triggers….
i want the output as below
DBName ObjectName ObjectType …..
Can anyone help me please
I have a phantom stored procedure that keeps running. How do i find and delete this phantom stored procedure?
What did you mean by phantom stored procedure?
Dear sir,
I have received one database from client i had made changes like, create,insert,update, delete, in table and stored procedures, could you please help me how to find the things which i had updated by using any query…