?
Solved

How to extract TABLENAMES from the ROUTINE_DEFINITION field in Stored Procedures?

Posted on 2014-03-12
6
Medium Priority
?
669 Views
Last Modified: 2014-03-13
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].information_schema.routines
 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
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39924276
From the routine text there is no simple way... consider comments, dynamic sql....
Also usages of views and other procedures will not make it easier.
Why do you want to do this?
0
 
LVL 17

Author Comment

by:MIKE
ID: 39924313
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
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39924549
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+'%'

Open in new window

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

Open in new window

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 17

Author Comment

by:MIKE
ID: 39924579
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].information_schema.routines
 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....
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39925121
Look into the DMVs to get the dependent objects inof.
sys.sql_expression_dependencies
http://technet.microsoft.com/en-us/library/bb677315.aspx

sys.dm_sql_referencing_entities
http://technet.microsoft.com/en-us/library/bb630351.aspx

sp_depends
http://technet.microsoft.com/en-us/library/ms189487.aspx
0
 
LVL 17

Author Closing Comment

by:MIKE
ID: 39926210
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!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question