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.
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 rowFROM sysdepends d INNER JOIN sysobjects o ON o.id=d.idINNER JOIN sysobjects oo ON oo.id=d.depidWHERE o.xtype = 'P' and o.name like 'usp_CareProviderDashBoard_MyPatients_PatientViewPaging')SELECT proc_name, table_name FROM stored_proceduresWHERE row = 1ORDER BY proc_name,table_nameit 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 |
|
|
|
|