Tuesday, 22 April 2014

How to Get List of Stored Procedures based on table name search, using sql server 2008?

Hi friends,in this article i will explain about Get List of Stored Procedures Where a Table Name is Used in SQL Server.
I already explained in the previous articles about Common Table Expressions(CTE) in SQL SERVER 2008,Create ,list and Alter the trigger associated with a table with SQL Server? and SQL Server 2008 XML input parameter to stored procedure and insert XML data to Table

Way 1:

SELECT distinct SO.name
FROM syscomments SC
INNER JOIN sysobjects SO ON SC.id=SO.id
WHERE sc.TEXT LIKE '%YourTableName%'


Example:


SELECT distinct SO.name
FROM syscomments SC
INNER JOIN sysobjects SO ON SC.id=SO.id
WHERE sc.TEXT LIKE '%user_details%'


The output of the above code as shown in the below figure.
How to Get List of Stored Procedures based on table name search, using sql server 2008?


Way 2:

SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourTableName%'



Example:


SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%user_details%'



The output of the above code as shown in the below figure.
How to Get List of Stored Procedures based on table name search, using sql server 2008?


Way 3:

EXEC sp_depends @objname = N'YourTableName';




Example:

EXEC sp_depends @objname = N'user_details';



The output of the above code as shown in the below figure.
How to Get List of Stored Procedures based on table name search, using sql server 2008?

"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook page Aspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter  on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on  RSSfeed Aspdotnet-Kishore  for free updates directly to your Email inbox . Watch my blog  for more articles."

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.