Search in a stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Search in a stored procedure

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-04-19 : 22:58:19

Is there any search method or query i can use to get the content of a stored procedures in a database.Eg: LTS database has 10 stored procedures and inside the stored procedure there are different select statement and I need to search a certain columns from the stored procedure.Is that possible

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-04-19 : 23:41:53

If I understand your needs correctly, you probably need to query the syscomments system table. The "text" column contains the contents of the stored procedures of that particular database. Keep in mind that multiple records may be needed to store a stored procedure's contents. Such records will have the same "id" and different "colid" values.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-04-19 : 23:59:58

select a.text from syscomments a , sysobjects b where a.id=b.id and b.type='P' where I need to get the user created stored procedure.Is this the correct query

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-20 : 01:29:50

Well, you probably also want to know which stored procedure it is, right?


DECLARE 
	@search_text_1 VARCHAR(255),
	@search_text_2 VARCHAR(255)
	

SELECT 
	@search_text_1 = 'name',
	@search_text_2 = NULL

SELECT 
	@search_text_1 = '%' + @search_text_1 + '%',
	@search_text_2 = '%' + @search_text_2 + '%'

SELECT
	so.name,
	sc.text
FROM
	sysobjects so
	INNER JOIN syscomments sc ON so.id = sc.id
WHERE
	so.xtype = 'P'
	AND sc.text LIKE @search_text_1
	AND (@search_text_2 IS NULL OR sc.text LIKE @search_text_2)
ORDER BY
	name,
	colid

Note: Logic like this becomes worthless pretty quickly if you have the same column name in a LOT of tables and haven't used a standard table alias or column prefix format.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2005-04-20 : 02:29:43

there are some third party tools like SQLDigger to facilitate your requirement

Kristen
Test

22859 Posts

Posted - 2005-04-20 : 07:38:44

Note that searching syscomments won't work if your SProcs were encrypted

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-20 : 07:47:34

This is an enhancement of Derrick's code, wrapped up in a stored procedure:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319