List out stored procedure use SQL table - Table use under stored procedure

In SQL Server if you want to find list of tables used by store procedures you can use below command.


EXEC sp_depends @objname = '<Stored Procedure Name>'

But let say you write dynamics query within SPs above command will eliminate those tables which are used in dynamic query. Below are sample query will return 100% accurate result.

Requirement :

List of Stored Procedures which are using those tables their name are started with PP.

Use <[Database Name]>


SELECT
     P.NAME,
     T.NAME
FROM SYSOBJECTS P 
     JOIN syscomments C ON P.ID = C.ID
     JOIN sysobjects T ON T.Name like 'PP%' AND T.[TYPE] = 'U' AND C.[TEXT] like('%'+T.Name+'%')
WHERE P.[TYPE] = 'P'
ORDER BY 1

Above query will work in SQL server 2000, 2005 & 2008 version

Comments

Post a Comment

Popular posts from this blog

SQL Server - Recursive Conditional Calculation.

I would like to share one SQL techniques to perform calculations recursively on SQL tables. This question is also very useful helpful in SQL Developer interview. Interviewer asked me below question. I have one table called CanalMaster. CanalMaster Contains all information related to Canal. Below are some information about CanalMaster table. There are two types of Canal Parent Canal - Doesn't have ParentCanalId Child Canal - have Canal Origin after Kilometer below is the SQL table. CREATE TABLE CanalMaster ( CanalId            numeric , -- Canal Unique Id CanalName          Varchar (100), -- Canal name ParentCanalId      numeric , -- Canal Parent Id CanalLength        numeric (10,2), -- Canal Length ChildCanalOriginKM numeric (10,2) -- Child Canal start origin from parent after kilometer. )    -- Sample Records. INSERT INTO CanalMaster VALUES (1, 'AAA' ,NULL,1000,NULL) INSERT INTO CanalMaster VALUES (2, 'BBB' ,1,700,30) INSERT INTO
Read more

CRM Report (SSRS) - Multi value parameter dropdown - horizontal scrollbar issue.

Image
·          CRM Report (SSRS) - Multi value parameter dropdown - horizontal scrollbar issue. Below JS code you need to paste it on reportviewer.aspx at end of the page Above ASPX page you can find underneath “CRMReports\rsviewer\”  folder at CRM Installed directory. <script type= "text/javascript" > function resizeListBox() {     var divElem = document.getElementById( 'ParametersRowreportViewer' ).firstChild.childNodes;     for ( var i = 0; i < divElem.length; i++)     {         if (divElem[i].tagName == 'DIV' && divElem[i].offsetWidth != 1000)         {             divElem[i].style.width = "350px" ;         }     } } var inputElem = document.getElementsByTagName( "input" ); for ( var i = 0; i < inputElem.length; i++) {     var imgId = inputElem[i].src     if (inputElem[i].type == "text" || (inputElem[i].type == "image" && imgId.indexOf( "MultiValueSelect.gif" ) != -1)
Read more

Microsoft Dynamic CRM 2016 / 2015 / 2013 / 2011 SOAP XML

Microsoft Dynamic CRM 2016 / 2015 / 2013 / 2011 SOAP XML  Dear Friends, Today I have been ask to help for create record in CRM from JAVA using SOAP. We have facing issue to create SOAP XML with different type. Kindly find below SOAP create request. Please shoot the question in below comment if you have any. <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:con="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:sys="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:c="http://www.w3.org/2001/XMLSchema">   <soapenv:Body>     <ser:Create>       <ser:entity>         <con:Attributes>           <!-- STRING DATATYPE-->           <con:KeyValuePairOfstringanyType>             <sys:key&
Read more