April 10, 2013 at 11:02 am
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 )
April 10, 2013 at 11:16 am
Is there a reason you cant do SELECT p.name FROM sys.procedures p ?
April 10, 2013 at 11:24 am
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
April 10, 2013 at 11:33 am
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
April 10, 2013 at 8:38 pm
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
April 10, 2013 at 9:13 pm
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
April 10, 2013 at 10:36 pm
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
April 12, 2013 at 1:06 pm
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
April 12, 2013 at 1:22 pm
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
April 12, 2013 at 1:35 pm
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
April 12, 2013 at 1:38 pm
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
April 15, 2013 at 12:52 pm
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