How to extract TABLENAMES from the ROUTINE_DEFINITION field in Stored Procedures?
Posted on 2014-03-12
What is the best function to use in order to extract the TABLENAMES that are used within a Stored Procedure's SQL Logic?
Here is the script that pulls the ENTIRE contents of the sprocs logic, as seen inthe ROUTINE_DEFINITION field:
select SPECIFIC_CATALOG as [Database], specific_schema as [Schema], SPECIFIC_NAME as [StoredProcedureName], ROUTINE_DEFINITION as [StoredProcLogic],created as [DateCreated], LAST_ALTERED as [LastAltered]
where routine_type = 'PROCEDURE'
and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
Of course its a TEXT field and I just need to know the best approach in trying to get each TABLENAME to display in a separate column. All tables in one column is ok with me....