t-sql trimming before and after stored proc name

  • Hi,

    I have requirement to get only stored proc name and trim everything before and after stored procedure name. Here is the code i tried. so output only need to show "abc_SessionValuesUpdate"

    Any help will be great...thanks...:-)

    declare @col varchar(20)

    select@col = 'CREATE Procedure abc_SessionValuesUpdate AS

    SET NOCOUNT ON

    BEGIN

    UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name

    IF @@ROWCOUNT = 0

    INSERT INTO SessionValues values (@ID,@Name,@Value)

    END'

    Select substring( LEFT(@Col,charindex('abc',@Col)+1),charindex

    ('abc_',@Col)+1,len(LEFT(@Col,charindex('abc',@Col)-1))+1 )

  • Is there a reason you cant do SELECT p.name FROM sys.procedures p ?

  • Thanks for the reply...actually i have following query and i want actual stored proc name instead of whole procudere on Last_executed_SQL column...

    SELECT ec.session_id, connect_time,s2.[text] as Last_Executed_SQL

    , auth_scheme as Auth_Type, client_net_address

    ,client_tcp_port, es.host_name, es.program_name, es.login_time, es.login_name

    FROM sys.dm_exec_connections ec

    OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2

    LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)

    WHERE s2.text LIKE ('%Stored procedure%')

    ORDER BY connect_time desc

  • Hi Kalpit,

    A quick solution is below.

    First, your @col variable was defined to small to hold the definition. That is the main problem I see here.

    I am assuming the procedure name is followed by '(' for parameters or 'as' if there are none.

    Sincerely

    John

    -- Declare larger string variable

    declare @col varchar(max);

    -- Start and end position of procedure name

    declare @start int;

    declare @end int;

    -- Set variable to dynamic code

    select @col = 'CREATE Procedure abc_SessionValuesUpdate (a int) AS

    SET NOCOUNT ON

    BEGIN

    UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name

    IF @@ROWCOUNT = 0

    INSERT INTO SessionValues values (@ID,@Name,@Value)

    END';

    -- Get start and end of name

    select @start = charindex('procedure ', @Col);

    select @end = charindex('(', @Col, @start + 10);

    if @end = 0

    select @end = charindex('as', @Col, @start + 10);

    -- Select just the proc name

    select substring(@col, @start + 10, @end-@start-10);

    John Miner
    Crafty DBA
    www.craftydba.com

  • It's pretty common to find strings like that in syscomments:

    /*

    The procedure updates values stored in table SessionValues

    */

    CREATE Procedure abc_SessionValuesUpdate (a int) AS

    SET NOCOUNT ON

    BEGIN

    UPDATE SessionValues SET Value = @Value WHERE ID = @ID AND Name = @Name

    IF @@ROWCOUNT = 0

    INSERT INTO SessionValues values (@ID,@Name,@Value)

    END

    The logic of the solution above will fail for all such cases.

    _____________
    Code for TallyGenerator

  • j.miner (4/10/2013)


    Hi Kalpit,

    A quick solution is below.

    John, I copied your syntax to my reply and only after posting I realised - it's wrong.

    Parameters in stored procedures do not require brackets:

    CREATE Procedure abc_SessionValuesUpdate @a int AS

    Not to mention - there might be no parameters, like in the initial post.

    Apart from that your solution does not consider following cases:

    CREATE Procedure abc_SessionValuesUpdate @a int AS

    CREATE

    Procedure

    abc_SessionValuesUpdate @a int AS

    CREATE Procedure

    --- some comment

    abc_SessionValuesUpdate @a int AS

    CREATE Procedure dbo.[abc_SessionValuesUpdate] @a int AS

    CREATE PROC dbo.[abc_SessionValuesUpdate] @a int AS

    and many-many more.

    _____________
    Code for TallyGenerator

  • kalpit_yellow (4/10/2013)


    Thanks for the reply...actually i have following query and i want actual stored proc name instead of whole procudere on Last_executed_SQL column...

    SELECT ec.session_id, connect_time,s2.[text] as Last_Executed_SQL

    , auth_scheme as Auth_Type, client_net_address

    ,client_tcp_port, es.host_name, es.program_name, es.login_time, es.login_name

    FROM sys.dm_exec_connections ec

    OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2

    LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)

    WHERE s2.text LIKE ('%Stored procedure%')

    ORDER BY connect_time desc

    You can join back to sys.objects via sys.sql_modules to get the stored procedure name. This will make the query only show running stored procedures. e.g.

    SELECTec.session_id,

    connect_time,

    s2.[text] as Last_Executed_SQL,

    auth_scheme as Auth_Type,

    client_net_address,

    client_tcp_port,

    es.host_name,

    es.program_name,

    es.login_time,

    es.login_name,

    ob.name as Stored_Procedure_Name

    FROM sys.dm_exec_connections ec

    OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2

    LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)

    JOIN sys.sql_modules sm on sm.definition = s2.text

    JOIN sys.objects ob on ob.object_id = sm.object_id

    ORDER BY connect_time desc

  • Hi Kalpit,

    The idea was to give you a general idea about the solution.

    Modify the code to your hearts content.

    The code below looks for the first 'procedure' marker and then the next space afterwards.

    It assumes that there is one space between the key word and procedure name.

    This does not handle nested definitions.

    Good luck

    John

    -- Get start and end of name, changed to look for second space

    select @start = charindex('procedure ', @Col);

    select @end = charindex(' ', @Col, @start + 10);

    John Miner
    Crafty DBA
    www.craftydba.com

  • another way is to use dbo.Delimitedsplit on sys.sql_modules.defintion, so every word in the code is separate, then simply join it to the name in sys.procedures.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell,

    Are you talking about the new [dbo].[DelimitedSplit8K] function Jeff Moden wrote.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Real cool stuff.

    John

    John Miner
    Crafty DBA
    www.craftydba.com

  • yeah, but there's another version that supports varchar(max), which is the datatype for sys.sql_modules.definition;

    it's effectively the exact same code but varchar(max) instead of varchar(8000)

    good for massive strings you need to manipulate, but slower, obviously.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you guys for all valuable feedback :-)...

    following Query from Mansfield working gr8 in my scenario!!

    SELECTec.session_id,

    connect_time,

    s2.[text] as Last_Executed_SQL,

    auth_scheme as Auth_Type,

    client_net_address,

    client_tcp_port,

    es.host_name,

    es.program_name,

    es.login_time,

    es.login_name,

    ob.name as Stored_Procedure_Name

    FROM sys.dm_exec_connections ec

    OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2

    LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)

    JOIN sys.sql_modules sm on sm.definition = s2.text

    JOIN sys.objects ob on ob.object_id = sm.object_id

    ORDER BY connect_time desc

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply