Stor Proc - Is there a way to extract definition quickly?
-
Hi,
MSSQL 7/2000
Is there a way I can quickly extract the code definition for a stored procedure? I want to create a cursor that will quickly loop through several sp's and their code definition to find if there is a CREATE VIEW statement within the sp?
Many thanks. Jeff
-
Select distinct O.Name FROM dbo.SysComments C inner join dbo.SysObjects O on C.id = O.id where Text like '%Create view%' and O.XType = 'P' Order By O.Name
-
The only problem being, of course, the 8000 char limit on the ctext column, and that fact that large stored procedures get broken up into multilpe syscomments records. If you're really unlucky, you can have the "CREATE VIEW" text right at 1 of the boundaries between code chunks, so you miss the occurrance.
-
I once wrote an application (in Visual Foxpro) to do this. It first uses sp_help to list all the objects in the target database, though queries directly against SysObjects would work as wel. It then ran sp_helpText for each stored procedure listed, then formatted and dumped the output into its own local storage area (memo column, but a file could work as well).
It took some time to write and debug, and on the larger databases it takes a while (1000+ procedures is a lot of text), but the ability to flawlessly search for any piece of text in any procedure in a large database is worth almost any price. (Plus I later subclassed it and worked it into some very useful DBA-style applications, but I digress.)
Odds are there's third-party software to do this, but rolling your own may be a better option. Generating scripts with Enterprise Manger is also an option, but their formatting is debatable. Doing it natively in T-SQl suggests itself (one row per procedure, load the script into a text file), but the interface (QA) is unwieldy.
Philip
-
Why the answer is right on the SSCentral website!
http://www.sqlservercentral.com/columnists/pressrelease/freewaresqldigger10.asp
SQL Digger to the rescue. http://sqldigger.bdsweb.be/
I download the .Net version of this little gem just the other day. It searches through SP's, views, and functions for strings that you specify. It's a version 1.0 product and could use some polishing, but it is stable and works as advertised. Plus it's free. Hard to beat that. It searches through our roughly 300 SP's in about 10 seconds.
Of course you could always just script out the DDL for all of your SP's to a single text file and just do a search for "create view"
---
Brian Dill -
Just for the records. You can also use the INFORMATION_SCHEMA views to get this information:
SELECT
ROUTINE_CATALOG
, ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped')=0
AND
ROUTINE_DEFINITION LIKE '%CREATE VIEW%'
Same limitations as direct querying the system tables.
Also, you won't see anything , that is NULL, when the stored procedure is encrypted.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Below is a vbscript i wrote to find a variable. If you want allthe procedures, don't enter anything for variable.
'This script will give the name of all the procedures wiht occurences of a specifc variable.
'It takes servername, uid, password and variable as the input parameters. If variable is null, it will give you all procedures.
'The script runs against master database. For any other database, change the value to sdatabase variable.
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)
if wscript.arguments.count > 3 then
var_find = wscript.arguments(3)
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
sdatabase = "master"
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
-
Bear with me. I'm not familiar with vbscript. How can I get this to run?
And, do you mind me, hosting it on my site?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry, should have mentioned that. Paste the script in a notepad and save it as find_procedure.vbs.
You can then run it from the command prompt and pass in the parameters next to it separated with a space. The output file is created in the same location where the script file is placed and is called output.txt. e.g.
Find_Procedure.vbs server_name uid password variable
-
Frank,
Feel free to post the script on the site.
Below is the updated version which also takes database name as a variable. and with all the instructions
'This script will give the text of all the procedures with occurences of a specifc variable.
'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.
'Copy the script below in a text file.
'Save it as find_procedure.vbs
'Open a command prompt and browse to the directory where the script is located.
'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
'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
-
Wow, thanks, I'm impressed! Think I should take a closer look at this scripting things
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply