Re: regex to find an stored proc name
- From: "Kevin Spencer" <kevin@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 18 Apr 2006 12:45:50 -0400
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...
.
- Follow-Ups:
- Re: regex to find an stored proc name
- From: kevin
- Re: regex to find an stored proc name
- References:
- Re: regex to find an stored proc name
- From: Kevin Spencer
- Re: regex to find an stored proc name
- Prev by Date: Docking Panels with Titlebars
- Next by Date: Re: Which codec is required?
- Previous by thread: Re: regex to find an stored proc name
- Next by thread: Re: regex to find an stored proc name
- Index(es):
Relevant Pages
|
Loading
