Procedure name filtering with table

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

Procedure name filtering with table

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-04-19 : 07:54:45

Hi All,

How can I get all the procedures of a DB,which includes a particular table name.eg:How do I get all the procedures,in which a table say 'Dept' includes.

Thanks & Regards
Binto Thomas

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-04-19 : 08:18:26

SELECT DISTINCT object_name(id) FROM sys.syscomments where text like '%dept%'

exec sp_depends 'dept'

binto
Yak Posting Veteran

59 Posts

Posted - 2010-04-19 : 08:24:16

Thanx a lot for your help

Thanks & Regards
Binto Thomas

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 09:07:36

"SELECT DISTINCT object_name(id) FROM sys.syscomments where text like '%dept%'"

I think syscomments is restricted to 8000 characters for the [text] column, so if an SProc has more than 8,000 characters it will be split over two records and a chance that the search string "dept" spans rows and thus isn't matched.

I don't think sys.sql_modules suffers from that problem:


SELECT	O.name
--	, M.definition
FROM	sys.sql_modules AS M
	JOIN sys.sysobjects AS O
		ON O.id = M.object_id
WHERE	O.type = 'P'
	AND M.definition LIKE '%dept%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-19 : 09:17:17

or

select routine_name from information_schema.routines
where object_definition(object_id(routine_name)) like '% dept%

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 09:31:27

I reckon information_schema.routines has limit of 8,000 ~~char~~bytes too

Why? I have absolutely no **** idea

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 09:33:57

Here's the definition in that VIEW:


	convert(nvarchar(4000),
		object_definition(o.object_id))	AS ROUTINE_DEFINITION,

What on earth were MS thinking of?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-19 : 09:39:12

quote:
Originally posted by Kristen

I reckon information_schema.routines has limit of 8,000 ~~char~~bytes too

Why? I have absolutely no **** idea

I used object_definition function over routine_name in the where clause and didn't make use of routine_definition

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 09:48:13

AH! So you did, my apologies. I wish I was as smart as you

(Young would be good too!)

Kristen
Test

22859 Posts

Posted - 2010-04-19 : 09:49:09

However ... what induced MS to put a CONVERT to get ROUTINE_DEFINITION in that View? Its just a trap for people to fall into

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources