regex to find an stored proc name

[kevin]

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.M yTable
and MyLinkedSrvr.My DB.MySchema.MyT able) and this will need to be used by
others.

The expression:
(?:(?:exec|exec ute)\s*(?:@\w*\ s*=)?)(?:\s*\[?\w*\]?\.)?(\s*\[?\w*\]?\.)?(\s*\[?\w*\]?)

--
kevin...

[Kevin Spencer]

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_IDENTIFI ER 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_IDENTIFI ER 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" <kw************ *********@fortk noxnational.com > wrote in message
news:01******** *************** ***********@mic rosoft.com...

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.M yTable
and MyLinkedSrvr.My DB.MySchema.MyT able) and this will need to be used by
others.

The expression:
(?:(?:exec|exec ute)\s*(?:@\w*\ s*=)?)(?:\s*\[?\w*\]?\.)?(\s*\[?\w*\]?\.)?(\s*\[?\w*\]?)

--
kevin...

[Kevin Spencer]

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" <ke***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:OR******** ******@TK2MSFTN GP04.phx.gbl...

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_IDENTIFI ER 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_IDENTIFI ER 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" <kw************ *********@fortk noxnational.com > wrote in message
news:01******** *************** ***********@mic rosoft.com...

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.M yTable
and MyLinkedSrvr.My DB.MySchema.MyT able) and this will need to be used by
others.

The expression:
(?:(?:exec|exec ute)\s*(?:@\w*\ s*=)?)(?:\s*\[?\w*\]?\.)?(\s*\[?\w*\]?\.)?(\s*\[?\w*\]?)

--
kevin...

[kevin]

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" <ke***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:OR******** ******@TK2MSFTN GP04.phx.gbl...

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_IDENTIFI ER 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_IDENTIFI ER 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" <kw************ *********@fortk noxnational.com > wrote in message
news:01******** *************** ***********@mic rosoft.com...

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.M yTable
and MyLinkedSrvr.My DB.MySchema.MyT able) and this will need to be used by
others.

The expression:
(?:(?:exec|exec ute)\s*(?:@\w*\ s*=)?)(?:\s*\[?\w*\]?\.)?(\s*\[?\w*\]?\.)?(\s*\[?\w*\]?)

--
kevin...

[Kevin Spencer]

> 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" <kw************ *********@fortk noxnational.com > wrote in message
news:C1******** *************** ***********@mic rosoft.com... 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" <ke***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:OR******** ******@TK2MSFTN GP04.phx.gbl...

> 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_IDENTIFI ER 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_IDENTIFI ER 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" <kw************ *********@fortk noxnational.com > wrote in message
> news:01******** *************** ***********@mic rosoft.com...
>>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.M yTable
>> and MyLinkedSrvr.My DB.MySchema.MyT able) and this will need to be used
>> by
>> others.
>>
>> The expression:
>> (?:(?:exec|exec ute)\s*(?:@\w*\ s*=)?)(?:\s*\[?\w*\]?\.)?(\s*\[?\w*\]?\.)?(\s*\[?\w*\]?)
>>
>> --
>> kevin...
>
>