Search ALL StoredProcs for a variable -- Please HELP
-
How can I find all StoredProcs that have "SHIPPING_TOTAL" text ?
I did the following and know at least 3 of my StoredProcs should display in the results, however none did.
1. Query Analyser
2. Tools
3. Object Search
4. New
5. Find "SHIPPING_TOTAL" in all StoredProcs
Please help.
-
Don't know why you didn't have success with the methods you mention, but try this:
declare @sql varchar(400)
set @sql='select ''?''as dbname;select * from information_schema.routines
where charindex(''shipping_total'',specific_name) 0'
exec sp_msforeachdb @sql
(Assuming you want to search all databases in a particular SQL Server occurrence.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Just to clarify here a little. Are you searching for a procedure with 'SHIPPING_TOTAL' in the name or in the text of the stored procedure? Also, is you server case-sensitive? Do you want to see all case variations of this?
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
text of StoredProc
yes, would like to see all case variations
-
take a look at : http://www.sqlservercentral.com/scripts/contributions/1130.asp
Keep in mind that this will not work on encrypted procs in the system.
If you are only looking for the sp's parameters, then you can query the [INFORMATION_SCHEMA].[PARAMETERS] systemview.
SELECT [SPECIFIC_CATALOG], [SPECIFIC_SCHEMA], [SPECIFIC_NAME], [ORDINAL_POSITION], [PARAMETER_MODE], [IS_RESULT], [AS_LOCATOR], [PARAMETER_NAME], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [INTERVAL_TYPE], [INTERVAL_PRECISION], [USER_DEFINED_TYPE_CATALOG], [USER_DEFINED_TYPE_SCHEMA], [USER_DEFINED_TYPE_NAME], [SCOPE_CATALOG], [SCOPE_SCHEMA], [SCOPE_NAME]
FROM [INFORMATION_SCHEMA].[PARAMETERS]
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you are trying to find any procs that mention SHIPPING_TOTAL try the following:
SELECT DISTINCT SO.name
FROM sysobjects SO
JOIN syscomments SC
ON SO.id = SC.id
WHERE SC.text LIKE '%SHIPPING_TOTAL%'
ORDER BY SO.name
-
Hello,
Hope this is the solution that u r looking for
SELECT DISTINCT SO.name
FROM sysobjects SO
JOIN syscomments SC
ON SO.id = SC.id
WHERE SC.text LIKE '%SHIPPING_TOTAL%' and so.xtype='P'
ORDER BY SO.name
Nivedita Sundar.N
-
Syscomments has a limitation of 4000 characters. You can use the script below. Paste it into a file and name it procedure.vbs(or anything else) and pass the parameters to the script to execute.
i.e. procedure servername username password database text_to_find
It will dump the output in a file called output.txt under the same location.
dim osqlserver
dim odatabase
dim sSQLserver
dim susername
dim spassword
dim var_find
ssqlserver = wscript.arguments(0)
susername = wscript.arguments(1)
spassword = wscript.arguments(2)
sdatabase = wscript.arguments(3)
if wscript.arguments.count > 4 then
var_find = wscript.arguments(4)
else
var_find = ""
end if
set osqlserver = createobject("SQLDMO.SQLServer")
set odatabase = createobject("SQLDMO.database")
dim fs
set fs = createobject("Scripting.filesystemobject")
dim output
set output = fs.createtextfile("output.txt",true)
osqlserver.connect sSQLServer, susername, spassword
set odatabase = osqlserver.databases(sdatabase)
for each storedprocedure in odatabase.storedprocedures
if instr(1,storedprocedure.text,var_find,1) > 0 then
output.writeline (storedprocedure.text)
end if
next
-
you don't need to join to sysobjects to get the name:
select object_name(id) from syscomments
where text like '%[text]%'
'Nuff Said..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply