Re: regex to find an stored proc name

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...


.

Relevant Pages

  • Re: EXECUTE tsqlstring with parameters
    ... I've tried using exec by itself, exec sp_executesql with an embedded exec ... string and sp_executesql with parameter substitution. ... until I need to execute a stored procedure that uses an OUTPUT parameter. ...
    (microsoft.public.sqlserver)
  • Re: Please Help
    ... I think if you use USE in a stored procedure, ... execute the entire statement dynamically with an EXEC - e.g. ... The database where the ...
    (microsoft.public.sqlserver.security)
  • RE: question on declare table
    ... you can not execute a stored procedure inside a function (you can ... extended ones) and you can not use EXEC to feed a table variable, ... > SELECT title, type, price ...
    (microsoft.public.sqlserver.programming)
  • Re: EXEC Command problem
    ... > Tried to do a search on EXEC and got a lot of hits, ... EXEC is just execute the stored procedure (the system stored procedure, ... EXECUTE permission denied on object 'sp_detach_db', database 'master', owner ... if your current user has not been granted membership to sysadmins server ...
    (microsoft.public.sqlserver.msde)
  • Re: exec/system wipes signal handler
    ... When you do an exec(), all of the code in memory ... new process image. ... array of pointers must be terminated by a NULL pointer. ... according to whether at least one file with suitable execute permissions ...
    (comp.lang.php)