Hello,
I would like to get a list of tables listed in a SQL SELECT statement. I
have not figured out how to code it nicely.

Sample statement:
SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...



I would like to see the result as:
table1
table2
table3

Thanks for your help,
ktmd

Try this

$sqlcmd = 'SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE
....'

## set to lower case
$sqlcmd = $sqlcmd.ToLower()

## drop the where and later
$i = $sqlcmd.Indexof("where")
$temp = $sqlcmd.substring(0,$i)

$temp = $temp.Replace("select * from ", "")

$array = $temp.split(",")

for ($i=0; $i -lt $array.count; $i++){

$array[$i] = $array[$i].Trim()

$j = -1
$j = $array[$i].Indexof(" ")

if ($j -ge 0){$array[$i] = $array[$i].substring(0,$j)}

$array[$i]
}


--
Richard Siddaway

Please note that all scripts are supplied "as is" and with no warranty


"ktmd" wrote:

> Hello,
> I would like to get a list of tables listed in a SQL SELECT statement. I
> have not figured out how to code it nicely.
>
> Sample statement:
> SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...
>
> I would like to see the result as:
> table1
> table2
> table3
>
> Thanks for your help,
> ktmd
>

Another variant using Regex:

$sqlcmd -match "FROM(.+)WHERE" #this will crop all text between FROM and
WHERE to $matches[1]
$m = [regex]::Match($matches[1],"[ ,]([\w\d]+?)[, ]") # select all words
surrounded by spaces or colons (not quotes)
while ($m.groups[1].value){$m.groups[1].value; $m=$m.nextmatch()} #iterate
through all matches

May look more complicated if you not familiar with regular expressions.

"ktmd" <ktmd@discussions.microsoft.com> wrote in message
news:81C9286F-C962-45E1-BC17-C17A1BC26637@microsoft.com...
> Hello,
> I would like to get a list of tables listed in a SQL SELECT statement. I
> have not figured out how to code it nicely.
>
> Sample statement:
> SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...
>
> I would like to see the result as:
> table1
> table2
> table3
>
> Thanks for your help,
> ktmd
>

Careful. It is easy to forget to make sure you can search across
lines. On top of that, Powershell is very tricky with newlines. And
on top of that, sometimes `n is treated exactly as `r`n which is
treated exactly as [Environment]::Newline.

One way to make sure your searches can "wrap" is to join all the lines
after Powershell has finished chomping them and stuffing them
individually in an array, but before performing the regex: % {
[string]::join(" ",$_) } There are many other ways to do this, though
I yet to develop a fully satisfying one.

Mike


On Jan 26, 6:47 am, "Vasily Gusev" <xae...@gmail.com> wrote:
> Another variant using Regex:
>
> $sqlcmd -match "FROM(.+)WHERE" #this will crop all text between FROM and
> WHERE to $matches[1]
> $m = [regex]::Match($matches[1],"[ ,]([\w\d]+?)[, ]") # select all words
> surrounded by spaces or colons (not quotes)
> while ($m.groups[1].value){$m.groups[1].value; $m=$m.nextmatch()} #iterate
> through all matches
>
> May look more complicated if you not familiar with regular expressions.
>
> "ktmd" <k...@discussions.microsoft.com> wrote in messagenews:81C9286F-C962-45E1-BC17-C17A1BC26637@microsoft.com...> Hello,
> > I would like to get a list of tables listed in a SQL SELECT statement. I
> > have not figured out how to code it nicely.
>
> > Sample statement:
> > SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...
>
> > I would like to see the result as:
> > table1
> > table2
> > table3
>
> > Thanks for your help,
> > ktmd

Thanks everyone for the responses. My problem was solved.
It is good to see multiple techniques to solve the problem.

ktmd

"mikes.net" wrote:

> Careful. It is easy to forget to make sure you can search across
> lines. On top of that, Powershell is very tricky with newlines. And
> on top of that, sometimes `n is treated exactly as `r`n which is
> treated exactly as [Environment]::Newline.
>
> One way to make sure your searches can "wrap" is to join all the lines
> after Powershell has finished chomping them and stuffing them
> individually in an array, but before performing the regex: % {
> [string]::join(" ",$_) } There are many other ways to do this, though
> I yet to develop a fully satisfying one.
>
> Mike
>
>
> On Jan 26, 6:47 am, "Vasily Gusev" <xae...@gmail.com> wrote:
> > Another variant using Regex:
> >
> > $sqlcmd -match "FROM(.+)WHERE" #this will crop all text between FROM and
> > WHERE to $matches[1]
> > $m = [regex]::Match($matches[1],"[ ,]([\w\d]+?)[, ]") # select all words
> > surrounded by spaces or colons (not quotes)
> > while ($m.groups[1].value){$m.groups[1].value; $m=$m.nextmatch()} #iterate
> > through all matches
> >
> > May look more complicated if you not familiar with regular expressions.
> >
> > "ktmd" <k...@discussions.microsoft.com> wrote in messagenews:81C9286F-C962-45E1-BC17-C17A1BC26637@microsoft.com...> Hello,
> > > I would like to get a list of tables listed in a SQL SELECT statement. I
> > > have not figured out how to code it nicely.
> >
> > > Sample statement:
> > > SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...
> >
> > > I would like to see the result as:
> > > table1
> > > table2
> > > table3
> >
> > > Thanks for your help,
> > > ktmd
>
>