Find Stored Procedure by Name

Find stored procedure by name

You can use:

select * 
from 
   sys.procedures 
where 
   name like '%name_of_proc%'

if you need the code you can look in the syscomments table

select text 
from 
    syscomments c
    inner join sys.procedures p on p.object_id = c.object_id
where 
    p.name like '%name_of_proc%'

Edit Update:

you can can also use the ansi standard version

SELECT * 
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_NAME LIKE '%name_of_proc%'

Search text in stored procedure in SQL Server

Escape the square brackets:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Then the square brackets will be treated as a string literals not as wild cards.

How to find stored procedures by name?

To find those that contain the string "Item" in the name.

select schema_name(schema_id) as [schema], 
       name
from sys.procedures
where name like '%Item%'

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

Query to list all stored procedures

As Mike stated, the best way is to use information_schema. As long as you're not in the master database, system stored procedures won't be returned.

SELECT * 
  FROM DatabaseName.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):

SELECT * 
  FROM [master].INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' 
   AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')

How to identify all stored procedures referring a particular table

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ

How do I find a stored procedure containing text?

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

---

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

---

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

Get the database name in which Stored Procedure exists

You can try this:

EXEC sp_msforeachdb 
'if exists(select 1 from [?].sys.objects where name=''SP_Email'')
select ''?'' as FoundInDatabase from [?].sys.objects where name=''SP_Email'''

Find Stored Procedure By Table Name (Oracle)

You can use:

select owner,
       job_name,
       job_style,
       job_type,
       program_name,
       job_action,
       start_date,
       repeat_interval,
       schedule_name,
       last_start_date,
       next_run_date,
       state
from   all_scheduler_jobs
WHERE  NEXT_RUN_DATE >= TRUNC(SYSDATE) + INTERVAL '27' HOUR
AND    NEXT_RUN_DATE <  TRUNC(SYSDATE) + INTERVAL '29' HOUR
order by
       owner,
       job_name;

To look for scheduled jobs that are next due to run after 03:00 tomorrow and before 05:00 tomorrow and then check the action that it invokes.

If that returns no rows then you can either widen the time range or remove the time filter and look at all the jobs.

Alternatively, you can check to see if a script is being run from the operating system via a cron job.

---

Related Topics