Re: regex to find an stored proc name
- From: "Kevin Spencer" <kevin@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 18 Apr 2006 15:54:05 -0400
One major flaw in yours though is that is doesn't handle the return type
as
in the following.
Darn, kevin, you're right. I overlooked that one.
--
HTH,
Kevin Spencer
Microsoft MVP
Professional Numbskull
Hard work is a medication for which
there is no placebo.
"kevin" <kwilliams_AINT_NO_FOOL_@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1C8B55C-2DAE-4BBB-811D-0D48C56A8A66@xxxxxxxxxxxxxxxx
Kevin,
Point well taken.
One major flaw in yours though is that is doesn't handle the return type
as
in the following.
I'll merge the two and thanks a lot for the input.
******************************************
--not matched
execute @return = this
exec [this]
EXEC [Sales by Year] '12/12/2001', '12/31/2004'
exec this a, b, c
execute [_a thing]
******************************************
kevin
--
kevin...
"Kevin Spencer" wrote:
Correction:
(?i)(?:execute|exec)\s+(?:(?:([#@_a-z][#@_$\.0-9a-z]*)(?!\]))|[\[]([#@_a-z][#@_$\.0-9a-z\s]*)[\]])
I had left the '.' character out of the allowable characters. I was going
by
the SQL Server Books Online reference, which didn't include it in the
lists
of allowable name characters (probably sinces it isn't exactly part of
the
name). Also, I had corrected myself where I had "1-9" and made it "0-9" -
but I had missed deleting the '1'. That didn't negatively affect the
results, but was superfluous.
--
HTH,
Kevin Spencer
Microsoft MVP
Professional Numbskull
Hard work is a medication for which
there is no placebo.
"Kevin Spencer" <kevin@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ORILNVwYGHA.4120@xxxxxxxxxxxxxxxxxxxxxxx
Hi kevin,
This is what you need:
(?i)(?:execute|exec)\s+(?:(?:([#@_a-z][#@_$10-9a-z]*)(?!\]))|[\[]([#@_a-z][#@_$10-9a-z\s]*)[\]])
If you test your original Regular Expression against the following, you
will see that its flaw lies in its inability to identify the complete
Stored Procedure Name if it has spaces in it:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
execute this
exec [this]
EXEC [Sales by Year] '12/12/2001', '12/31/2004'
exec this a, b, c
execute [_a thing]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The one I gave you will do this, and will put the Stored Procedure
names
into Group 1 and Group 2 respectively, without the square brackets.
--
HTH,
Kevin Spencer
Microsoft MVP
Professional Numbskull
Hard work is a medication for which
there is no placebo.
"kevin" <kwilliams_AINT_NO_FOOL_@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:0134A663-14A7-4EA7-8397-784185DC1D2B@xxxxxxxxxxxxxxxx
I want to return a list of every sp that is called by a given sp, and
sp_depends is anything but...
The user will have to supply the name of the first/top level SP and
then
my
app will do the rest from there.
My question:
Will the following expression always work. I have tested it and it
works
now, but I am very consistant in my naming convention in SQL SERVER:
I
explicitly use a fully qualified name (3 or 4 part e.g:
MyDB.MySchema.MyTable
and MyLinkedSrvr.MyDB.MySchema.MyTable) and this will need to be used
by
others.
The expression:
(?:(?:exec|execute)\s*(?:@\w*\s*=)?)(?:\s*\[?\w*\]?\.)?(\s*\[?\w*\]?\.)?(\s*\[?\w*\]?)
--
kevin...
.
- References:
- Re: regex to find an stored proc name
- From: Kevin Spencer
- Re: regex to find an stored proc name
- From: Kevin Spencer
- Re: regex to find an stored proc name
- From: kevin
- Re: regex to find an stored proc name
- Prev by Date: Re: ArrayList.BinarySearch returns various negative values if item not found
- Next by Date: Re: Which codec is required?
- Previous by thread: Re: regex to find an stored proc name
- Next by thread: Docking Panels with Titlebars
- Index(es):
Relevant Pages
|
