Finding Procedures with a specified text

sa24, 2005-03-03 (first published: 2005-02-25)

This script will give the text of all the procedures with occurences of a specifc variable. Syscomments has a limitation of 4000 characters but this script overcomes that as it uses DMO. The output is generated in a text file. If you want the name of the procedure only. replace the line where it says output.writeline (storedprocedure.text) to output.writeline(storedprocedure.name).Its the fourth line from the end of the file.
Below are the steps for deployment
1) Copy the script below in a text file.
2) Save it as find_procedure.vbs
3) Open a command prompt and browse to the directory where the script is located.
4) It takes servername, uid, password, databasename and variable as the input parameters. Separate each value with a space. You can use double quotes for variables with space.
e.g. find_procedure.vbs server_name uid password database variable
5) The output of the script is generated in the file output.txt

dim osqlserver dim odatabase dim sSQLserver dim susername dim spassword dim var_find on error resume next if wscript.arguments.count < 4 then if err.number <> 0 then output.writeline("Please enter all the values i.e. Servername, username, password, database. Variable name is optional") end if else 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 if err.number <> 0 then output.writeline("Error generated " & err.description) end if set odatabase = osqlserver.databases(sdatabase) if err.number <> 0 then output.writeline("Error generated " & err.description) end if for each storedprocedure in odatabase.storedprocedures if instr(1,storedprocedure.text,var_find,1) > 0 then output.writeline (storedprocedure.text) end if next end if