September 17, 2008 at 12:39 pm
how can we Identify in how many Storedprocedures has a sp been called.
Ex:
Create procedure Proc1
Exec Proc2
End
Create procedure Proc3
Exec Proc2
End
I want proc1 and proc3 to be listed as they are calling proc2.
Is it possible.Please provide me with a solution.?
September 17, 2008 at 1:01 pm
The most direct way I know of is to look at the "Object Execution Statistics". It will give you execution stats on all executable objects including among others, how many times it's been run.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 18, 2008 at 1:07 am
Hi there!
Heres an easy way
Just create a table which will be used to store the stored procedures name or Id and when it was executed.
Now, in every sproc, just record in the table that the particular sproc was executed.
How to manage that table... Its up to you
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 18, 2008 at 1:16 am
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.
"Keep Trying"
September 18, 2008 at 1:49 am
run this:-
select text from sys.syscomments
where text like '%Exec Proc2%'
September 18, 2008 at 2:13 am
Chirag (9/18/2008)
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.
sysdepends isn't always accurate, especially in cases where the outer proc was created before the inner procs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 18, 2008 at 4:11 am
After little bit of google search i came up with the following....
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SPname%'
UNION
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Exec dbo.spName%'
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE Definition LIKE '%Exec spname%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES where Specific_NAme='Custom_EditTrancheDeal'
WHERE ROUTINE_DEFINITION LIKE '%Exec dbo.spname%'
AND ROUTINE_TYPE = 'PROCEDURE'
September 18, 2008 at 5:50 am
Yet another option is to look at the execution plans. sys.dm_exec_cached_plans has a column usecounts that shows how many times a plan has been used. This won't be perfect because the plan may have aged out of cache or the procedure may have language that causes a recompile every time. It should give you a ballpark figure.
If you really want exact numbers, run a trace to file and capture everything. You can then load it back to a database and run aggregate queries against it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2008 at 12:55 am
GilaMonster (9/18/2008)
Chirag (9/18/2008)
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.sysdepends isn't always accurate, especially in cases where the outer proc was created before the inner procs.
Yes you are right. They have corrected this in SQL Server 2008 though.
"Keep Trying"
September 19, 2008 at 5:43 am
Chirag (9/19/2008)
GilaMonster (9/18/2008)
Chirag (9/18/2008)
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.sysdepends isn't always accurate, especially in cases where the outer proc was created before the inner procs.
Yes you are right. They have corrected this in SQL Server 2008 though.
MS doesn't want us using sysdepends any more though, right? sys.sql_expression_dependencies, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are all the new mechanisms for dependency tracking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2008 at 5:59 am
Yup. sysdepends is as inaccurate as ever in 2008, and deprecated to boot. It hasn't been fixed, it's been replaced
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 6:11 am
can any body tell me advantages of system catalog views over system tables ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 19, 2008 at 6:42 am
There really isn't any. The thing to remember is, that prior to 2005, when you queried the system tables, you were querying the system tables. Since 2005, all the things that look like system tables are actually views on top of the real tables. This is especially true of the tables like sysdepends which have been replaced in functionality with other processes. These views/tables are only maintained for backwards compatibility.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2008 at 6:48 am
In 2005 and higher the system tables are no longer visible and cannot be queried.
Objects like sysobjects, sysdepends, etc are known as compatibility views and are only included for backwards compatability with SQL 2000. They are deprecated and will be dropped in a future version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 7:43 pm
Chirag (9/18/2008)
another way is to right click on the proc in SSMS and select view dependencies. It will show all objects that depend on the proc and also all objects that the proc depends on.
I'll have to double check, but I don't believe that's true. I believe it shows only the first level dependencies. If a ProcA calls ProcB which calls ProcC, I'm pretty sure that ProcC won't show up in the dependencies list.
Umm... kind of and not really... it shows only the first level dependencies. If a ProcA calls ProcB which calls ProcC, ProcC won't show up in the dependencies list for ProcA.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply