0

I have sql stored procedure code in a string variable in Excel VBA, I am trying to get list of all table names used in this stored procedure. So each table starts with a schema name Prop.Table1 , Prop.Table2. I am doing a instr and trying to get the occurrence of Prop but how do I get the full name like Prop.Table1 , I cannot just use a mid because the table name would not always be followed by a space (it can be a line feed or space)

Input: 

create proc test 
as 
begin
select * from Prop.Table1
join Prop.Table2 p
on Prop.Table1.col1=p.col1
end

Output:

Prop.Table1
Prop.Table2

I don't mind getting duplicate table names in my output.

Community
  • 1
  • 1
Joseph
  • 530
  • 3
  • 15
  • 37
  • I dont mind getting duplicate table names in my output – Joseph Aug 16 '17 at 07:45
  • 1
    Could you post what you already have to get the `Prop` part of each occurence, so others complement that? – sancho.s ReinstateMonicaCellio Aug 16 '17 at 08:29
  • what is the database? edit question with the name of database vendor, you might find that a clever database expert could point to a database function that can list the tables upon which the stored proc is dependent. – S Meaden Aug 16 '17 at 11:36
  • Joseph, did you find any contribution useful? Please remember giving feedback. – sancho.s ReinstateMonicaCellio Oct 26 '17 at 04:45
  • No I didnt actually. There were other complications in my source code, like for example few table names were referred without schema names, so it was difficult to figure them out. I didn't had the liberty to use any database in build stored procs. – Joseph Oct 30 '17 at 13:46

1 Answers1

0

You want to deal with all possible "environments" where your target string Prop.TableX may show up, and you provided three sample cases (followed by newline, space, and dot). So you want to:

  1. Split your text into words using multiple delimiters. This is probably the hardest part. There are many options around, each with its pitfall. Bullet-proof code for this is provided by the great Chip Pearson.
  2. Find all occurrences stri of Prop. This has been asked many times. As per your OP, I asume you have this. Just in case, you could adapt this answer. See below
  3. Keep stri plus the following word strj of each Prop. This is easy.
  4. Concatenate stri (=Prop), "." and strj (=TableX). This is easy.

You could invert the order of items 1 and 2, with minor variations. E.g., splitting each time the remainder of the source string to find the second part of the substring.

Code for ítem 2, assuming s contains your source string where to look for Prop.

Dim srch As String
srch = "Prop"

Dim start As Integer, pos As Integer
start = 1
Do
  pos = InStr(start, s, srch, vbTextCompare)
  If pos > 0 Then
    start = pos + 1  'alternatively: start = pos + Len(srch)
    Debug.Print pos
    Debug.Print Mid(s, pos, Len(srch))
  End If
Loop While pos > 0
Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185