Need the list of SPs - 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

General SQL Server Forums

New to SQL Server Programming

Need the list of SPs

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2012-04-17 : 06:04:48

I have a database named "ABC". I need the list of Stored Procedures of that database that contains the query:

"Delete from xyz " . [Here "xyz" is the table name.]

Can someone please help me.

kalyan Ashis Dey

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-04-17 : 06:32:24

Something like this:


DECLARE @SearchString VARCHAR(255) = 'DELETE FROM'

SELECT
	[ObjectName]
	, [ObjectText]
FROM
	(
	SELECT
		so.[name] AS [ObjectName]
		, REPLACE(comments.[c], '#x0D;', '') AS [ObjectText]
	FROM
		sys.objects AS so
		CROSS APPLY ( 
			SELECT CAST([text] AS NVARCHAR(MAX))
			FROM syscomments AS sc
			WHERE sc.[id] = so.[object_id]
			FOR XML PATH('')
			)
			AS comments ([c])
	WHERE
		so.[is_ms_shipped] = 0
		AND so.[type] = 'P'
	)
	AS spText
WHERE
	spText.[ObjectText] LIKE '%' + @SearchString + '%'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2012-04-17 : 06:44:38

Thanks a lot for your help .. :)

kalyan Ashis Dey

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-17 : 06:46:15

Very interesting. I ran this

select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%DELETE FROM%'

and got a different number of procs than when i ran your code Charlie. Yours returned more.
Any reason why that would be?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-17 : 06:53:03

quote:
Originally posted by DonAtWork

Very interesting. I ran this
select * from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%DELETE FROM%'
and got a different number of procs than when i ran your code Charlie. Yours returned more.
Any reason why that would be?

If you got more rows than Charlie did, one reason could be that INFORMATION_SCHEMA.ROUTINES includes stored procs and functions.

What I normally use is object_definition(object_id) from sys.objects or definition column from sys.sql_modules

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-17 : 06:55:14

No, i got less than Charlie's code. I suppose i could look at the actual proc names and see which are missing. I was just hoping i could be lazy and get an answer.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-17 : 07:03:17

ok found it. Routine_definition is a nvarchar(4000).

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-04-17 : 11:53:50

Ah cool.

My code is actually reverse engineered from sp_helptext.

If you run:


EXEC sp_helptext 'sp_helptext'

(in a fit of recursion)
You'll see a *HORRIFIC* cursor

Also a sp isn't really all that nice for this kind of thing so I turned it into a table valued function. This is the core logic of that function.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources