Search for a string in objects like sp,functions..

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 2008 Forums

Transact-SQL (2008)

Search for a string in objects like sp,functions..

Author

Topic

learntsql

524 Posts

Posted - 2012-08-10 : 05:34:47

Hi All,

I need to search for the string in all databse objects like procedures,Functions,views,etc...
In all these i want to search for parameters and variables if this string is used.
TIA

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-10 : 05:44:47

Script them out and then use the tool of your choice. Agent Ransack is good (if you like regular expressions) but a simple windows find would do it.:

http://download.cnet.com/Agent-Ransack/3000-2072_4-10043846.html

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-10 : 05:51:46

SELECT sys.schemas.name + '.' + OBJECT_NAME(id) AS ObjectName
FROM syscomments
INNER JOIN sys.objects ON syscomments.id = sys.objects.object_id
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE [text] LIKE N'%MySearchTextHere%'
AND ( OBJECTPROPERTY(id, 'IsProcedure') = 1
OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1
OR OBJECTPROPERTY(id, 'IsView') = 1
)
GROUP BY sys.schemas.name + '.' + OBJECT_NAME(id)

--------------------------
http://connectsql.blogspot.com/

learntsql

524 Posts

Posted - 2012-08-10 : 08:48:00

ThankQ guys.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-08-10 : 10:09:16

If u need to do it frequently, use a tool (free) like SQL Search from RedGate.

Srinika

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 10:52:35

quote:
Originally posted by learntsql

Hi All,

I need to search for the string in all databse objects like procedures,Functions,views,etc...
In all these i want to search for parameters and variables if this string is used.
TIA

select object_name(object_id)
from sys.sql_modules
where definition like '% <the string value> %'

?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-10 : 11:42:04

quote:

select object_name(object_id)
from sys.sql_modules
where definition like '% <the string value> %'

sys.sql_modules truncates text. Sure that's come up on this forum before.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:54:38

quote:
Originally posted by Transact Charlie

quote:

select object_name(object_id)
from sys.sql_modules
where definition like '% <the string value> %'

sys.sql_modules truncates text. Sure that's come up on this forum before.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/

nope that was sys.syscomments as its text field was limited to nvarchar(4000)

sys.sql_modules has definition field as nvarchar(max) so i dont think it does any truncation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-13 : 04:14:16

ah cool. thanks for the correction. You are absolutely correct: And this is troubling......


SELECT MAX(LEN([definition])) FROM sys.sql_modules

Results:

WTF?

Time to go hunting.........

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 07:48:41

Just means you have been working very hard and writing some very long stored procedures

I thought I had a few long stored procs (1,500 lines of code) and my max length is only 47,031.

I know they give the same results, but I always end up using object_defintion(object_id) from sys.objects to do this type of search.

SELECT MAX(LEN(OBJECT_DEFINITION(OBJECT_ID))) FROM sys.objects

sys.sql_modules is built on top of sys.objects and the definition column in sys.sql_modules uses that anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:05:20

quote:
Originally posted by sunitabeck

Just means you have been working very hard and writing some very long stored procedures

I thought I had a few long stored procs (1,500 lines of code) and my max length is only 47,031.

I know they give the same results, but I always end up using object_defintion(object_id) from sys.objects to do this type of search.
SELECT MAX(LEN(OBJECT_DEFINITION(OBJECT_ID))) FROM sys.objects
sys.sql_modules is built on top of sys.objects and the definition column in sys.sql_modules uses that anyway.

yep...thats true
i somehow seems to prefer sys.sql_modules always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-13 : 15:50:51

quote:
Originally posted by sunitabeck

Just means you have been working very hard and writing some very long stored procedures

Kind of you.... but if I'd written one stored proc something with over 1 MILLION characters of code I'd have to take myself out back and shoot myself.

For my own good.

Turns out that this was an auto generated sp written back in the dawn of time to migrate servers. No idea why it was done this way.... It's been sitting in our svn repo and being deployed to every environment even though it's never, ever used....

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources