SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to find list of tables used in singlstoreProc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/18/2013 :  03:05:51  Show Profile  Reply with Quote
Hello all,

here i need to find out list of tables in a store procedure .Like how many are there inside the Store Procedure then i searched and found out this query 

[Code]
;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P' and o.name like 'usp_CareProviderDashBoard_MyPatients_PatientViewPaging')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name


it is exactly showing what i needed giving the list of tables inside store procedure....but in my procedure tables are dynamically passed
for i will post one example because it is having 4000 lines of code..


IF EXISTS ( SELECT 1 FROM @t_tProgramID )
BEGIN

SET @v_SQLCareTeam = ' INNER JOIN ( SELECT distinct UserPrograms.UserId from UserPrograms WITH(NOLOCK)
INNER JOIN ProgramCareTeam WITH(NOLOCK)
ON ProgramCareTeam.ProgramID = UserPrograms.ProgramID
INNER JOIN CareTeamMembers WITH(NOLOCK)
ON CareTeamMembers.CareTeamID = ProgramCareTeam.CareTeamID
INNER JOIN #MYTEMP TEMP1
ON UserPrograms.ProgramId = TEMP1.ProgramID
WHERE TEMP1.ProgramID IS NOT NULL
AND CareTeamMembers.UserId = ' + CONVERT(VARCHAR,@i_AppUserId) + '
AND UserPrograms.StatusCode = ''' + 'A' + '''
AND CareTeamMembers.StatusCode = ''' + 'A' + ''' '+ ISNULL(@v_CareTeam,'')+ '

) DerivedPatients
ON DerivedPatients.UserID = Patients.UserId
AND Patients.UserStatusCode = ''' + 'A' + ''''
END
[/Code]
here they are in single quotes and dynamically passed
P.V.P.MOhan

Edited by - mohan123 on 02/18/2013 03:06:48

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/18/2013 :  03:18:04  Show Profile  Reply with Quote
for that you've to use sys.sql_modules view

see

http://visakhm.blogspot.in/2012/03/advantages-of-using-syssqlmodules-view.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000