| Author |
 Topic  |
|
|
learntsql
Constraint Violating Yak Guru
India
460 Posts |
 Posted - 08/10/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
|
|
lionofdezert
Aged Yak Warrior
Pakistan
864 Posts |
Posted - 08/10/2012 : 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
Constraint Violating Yak Guru
India
460 Posts |
Posted - 08/10/2012 : 08:48:00
|
| ThankQ guys. |
|
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 08/10/2012 : 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
India
47188 Posts |
Posted - 08/10/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 08/10/2012 : 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
India
47188 Posts |
Posted - 08/10/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 08/13/2012 : 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:
1185118
WTF?
Time to go hunting.........
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/13/2012 : 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
India
47188 Posts |
Posted - 08/13/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3442 Posts |
Posted - 08/13/2012 : 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/ |
|
|
| |
Topic |
|
Search for a string in objects like sp,functions..
