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
How to find list of tables used in singlstoreProc
Posted - 02/18/2013 : 03:05:51
