Marcus Aurelius
asked on
How to extract TABLENAMES from the ROUTINE_DEFINITION field in Stored Procedures?
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]
from [Your_Database].informatio n_schema.r outines
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....
Thx
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]
from [Your_Database].informatio
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....
Thx
ASKER
All I'm asking is what FUNCTION is easiest to pull tablenames from this TEXT Field.
ie. SUBSTRING()??, CHARINDEX()??
The text is basically SQL SCRIPTS that contain the standard SELECT, FROM, WHERE syntax. I would like to use a function to find the "FROM" section of each SQL Script and extract WHATEVER comes after that "FROM" clause, realizing that there are also "LEFT/INNER" JOINS etc...
Any Expert commments/input would be greatly appreciated.
Oh and the reason I need this is I'm on a project of REFACTORING an entire Data Warehouse/Data Mart over to a totally new Data Warehouse and need to MAP ALL TABLES AND FIELDS from one DW to another DW.....
thanks
ie. SUBSTRING()??, CHARINDEX()??
The text is basically SQL SCRIPTS that contain the standard SELECT, FROM, WHERE syntax. I would like to use a function to find the "FROM" section of each SQL Script and extract WHATEVER comes after that "FROM" clause, realizing that there are also "LEFT/INNER" JOINS etc...
Any Expert commments/input would be greatly appreciated.
Oh and the reason I need this is I'm on a project of REFACTORING an entire Data Warehouse/Data Mart over to a totally new Data Warehouse and need to MAP ALL TABLES AND FIELDS from one DW to another DW.....
thanks
The best way is to use LIKE:
declare
@str_to_search varchar(8000)='TableName'
select r.ROUTINE_SCHEMA as [SCHEMA], r.ROUTINE_NAME as [OBJECT_NAME], r.ROUTINE_TYPE as [OBJECT_TYPE],r.ROUTINE_DEFINITION CODE from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_DEFINITION like '%'+@str_to_search+'%'
You then put this code in a loop that will go through all the tables in a database which you can get from:select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' order by 1
ASKER
Thank you for yout input, but ... I'm actually seeking code that will FIND the TABLENAME within the ROUTINE_DEFINITION field and then display this TABLENAME(s) is a separate column. Make sense?
I need something like this:
select SPECIFIC_CATALOG as [Database]
, specific_schema as [Schema]
, SPECIFIC_NAME as [StoredProcedureName]
, ROUTINE_DEFINITION as [StoredProcLogic]
,created as [DateCreated]
, LAST_ALTERED as [LastAltered]
, *** 'some crazy text search logic HERE' as [TableName from SPROC] ***
from [Your_Database].informatio n_schema.r outines
where routine_type = 'PROCEDURE'
and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
Hope it makes sense, I need to EXTRACT the TableName(s) that are found within the FROM clause of each SQL Script within a Stored Procs logic text. Extract it and place it in its own column....
I need something like this:
select SPECIFIC_CATALOG as [Database]
, specific_schema as [Schema]
, SPECIFIC_NAME as [StoredProcedureName]
, ROUTINE_DEFINITION as [StoredProcLogic]
,created as [DateCreated]
, LAST_ALTERED as [LastAltered]
, *** 'some crazy text search logic HERE' as [TableName from SPROC] ***
from [Your_Database].informatio
where routine_type = 'PROCEDURE'
and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
Hope it makes sense, I need to EXTRACT the TableName(s) that are found within the FROM clause of each SQL Script within a Stored Procs logic text. Extract it and place it in its own column....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sharath,
You're a ROCKSTAR! This is exactly the kind of information that I was looking for!
I'll be using this specific link:
sp_depends
http://technet.microsoft.com/en-us/library/ms189487.aspx
MANY Thanks for responding!
You're a ROCKSTAR! This is exactly the kind of information that I was looking for!
I'll be using this specific link:
sp_depends
http://technet.microsoft.com/en-us/library/ms189487.aspx
MANY Thanks for responding!
Also usages of views and other procedures will not make it easier.
Why do you want to do this?