April 25, 2008 at 8:07 am
Does sql server support the use of regular expressions for string matching in stored procedures? I couldn't find anything in books online...
If it doesn't, what is the best way to parse out a string? I am trying to generate stubs for stroed procedures programatically, so I need to pull the procedure name, its parameters(ins and outs), and the data type of each parameter. Are there any scripts out there that will do this?
Thank you.
April 25, 2008 at 8:15 am
Well as with many situations - the answer to "the best way to parse" will be - it depends. T-SQL has some fairly decent built-in functions, but they fall short in the "complex pattern matching" category. In other words - it's probably worth seeing if you can do your parsing with the built-in functions, and if not, THEN trot out regex.
Best way in 2005 is to create CLR functions to allow for regex functionality. I have a whole battery of them that I use. Now - because it's CLR - you don't even want to bother setting this up if you're not on SP2 or later (from what I saw - the memory issues before that just made it unstable/unreliable).
Once you enable CLR functionality in the surface area configuration tool, you could build and deploy something like this to your server (this is a wrapper for the Regex "replace" function):
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function RegexReplace(ByVal input As SqlChars, ByVal pattern As SqlString, ByVal rep As SqlString) As SqlString
' Add your code here
Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value)
If input.IsNull Then
Return New SqlString(SqlString.Null.Value)
Else
Return New SqlString(rex.Replace(New String(input.Value), rep.Value))
End If
End Function
End Class
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 25, 2008 at 9:01 am
System views can give you this information without having to roll your own pattern matching.
sys.procedures has all the procs listed in it.
sys.all_parameters has the parameters.
sys.types has the data types.
select procs.name as ProcName, params.name as ParameterName, types.name as ParamType,
params.max_length, params.precision, params.scale, params.is_output
from sys.procedures procs
left outer join sys.all_allparameters params
on procs.object_id = params.object_id
left outer join sys.types
on params.system_type_id = types.system_type_id
and params.object_id is not null
That should give you what you want. Play with it a bit to add/remove any columns you want to change.
Edit: Might be better to move the parameters and their types into a CTE, and left join to that. It's just to get any procs that don't have parameters, if you have any of those. If you don't have procs that no parameters, you can change both of those to inner joins.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2008 at 9:26 am
Awesome. Thank you.
April 25, 2008 at 9:47 am
I didn't even see then secondary question, focusing instead on the "using regular expressions" from the title. I didn't notice what you were planning on using it for....
GSquared is right - you may care to look at the system views first - there's a lot there for you to refer to....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 25, 2008 at 3:42 pm
I had to modify the query a bit to get it to run. Here is what I got:
select procs.name as ProcName, params.name as ParameterName, types.name as ParamType,
params.max_length, params.precision, params.scale, params.is_output
from sys.procedures procs
left outer join sys.all_parameters params
on procs.object_id = params.object_id
left outer join sys.types types
on params.system_type_id = types.system_type_id
and params.object_id is not null
The only problem is, I am getting many rows with:
1. the same ProcName(expected and that's ok)
2. the same ParameterName over and over, with each entry having a different value in the paramtype column
What am I doing wrong?
Thank you.
April 28, 2008 at 7:57 am
I'm not sure.
I just tried running your exact query (just copy-and-paste into Management Studio), and it ran perfectly on my server.
Try this:
;with Params (ParameterName, ParamType, Max_Length, [Precision],
[Scale], Is_Output, OID) as
(select par.name, types.name, par.max_length, par.precision,
par.scale, par.is_output, par.[object_id]
from sys.all_parameters par
inner join sys.types types
on par.system_type_id = types.system_type_id)
select procs.name as ProcName, ParameterName, ParamType,
params.max_length, params.precision, params.scale, params.is_output
from sys.procedures procs
left outer join params
on procs.object_id = params.oid
See if that fixes it for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 8:03 am
Here's what I came up with so far:
SELECTprocs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROMsys.procedures procs
LEFT OUTER JOINsys.all_parameters params
ONprocs.object_id = params.object_id
LEFT OUTER JOINsys.types types
ONparams.system_type_id = types.system_type_id
WHEREparams.user_type_id = types.user_type_id
ANDprocs.is_ms_shipped = 0
ORDER BYprocname,
params.parameter_id
The only problem is that I'm not returning the stored procedures without parameters. I've tried removing the first where clause but that cuases each parameter to be returned over and over as each possible datatype. I thought the outer joins would return what I want, but it's not working.
April 28, 2008 at 8:11 am
Did you try the CTE version I just posted? It returns procs that don't have parameters, just leaves those columns null.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 8:14 am
The version you posted does the same thing: several rows for each parameter with the parameter as a different data type.
I figured it out, just had to change around the condition:
SELECTprocs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output
FROMsys.procedures procs
LEFT OUTER JOINsys.all_parameters params
ONprocs.object_id = params.object_id
LEFT OUTER JOINsys.types types
ONparams.system_type_id = types.system_type_id
ANDparams.user_type_id = types.user_type_id
WHEREprocs.is_ms_shipped = 0
ORDER BYprocname,
params.parameter_id
Thank you for all the help, though.
April 28, 2008 at 8:15 am
Glad you got it working.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply