How to find list of tables used in singlstoreProc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to find list of tables used in singlstoreProc

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-18 : 03:05:51

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 03:18:04

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources