How can I extract all databasename.tablename found in all .sql queries in a folder using Powershell?

Question

I want to extract all the list of databaseName.tableName from all my SQL queries after from or join and dedupe them, put them into a 2 column CSV file with FileName, Database.TableName list. I do have sometime schema in between database and table names, I may want to remove that part.

For example:

File#1 = Create table xyz as select * from abc.bcd;
File#2 = Create table sdf as select * from asd.fgh;

I wanted:

FileName   Table
File#1     abc.bcd
File#2     asd.fgh

This is what I tried -

Select-String -Path "\shared\path*.sql" -Pattern 'from|join|update|into\s(\w{100})'

But not getting exact output needed, more info in the comment.

As many will tell you SO is not a script-writing service. SO has rules [Provide MRE](https://stackoverflow.com/help/minimal-reproducible-example) --- [How to ask](https://stackoverflow.com/help/how-to-ask) --- [Don't ask](https://stackoverflow.com/help/dont-ask) --- [Proper Topic](https://stackoverflow.com/help/on-topic) --- [Why not upload images of code/errors?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) --- [format your post properly](https://stackoverflow.com/help/formatting) — postanote, Sep 17 '22 at 07:00
You are not showing any code. What you are after, string extraction is a very common thing. Just use a RegEx to select the portion of each string you do/don't want, then format for output (CSV/tabular). — postanote, Sep 17 '22 at 07:36

score 0 · Answer 1 · answered Sep 17 '22 at 20:23

I spent already hours of time and that is why asked - sorry I didn't include what I tried -

Select-String -Path "\shared\path*.sql" -Pattern 'from|join|update|into\s(\w{100})'

This gave something like

file1.sql:795: left join abcd.abcdefgh_ijkl kk file1.sql:798: left join abcd.hjklmnop_qrst uu

and whole lot more of these above ones.

How can I only return 1 entire string (databasename.tablename or database.schema.table) after from/join/update/into but stop after the table name ends (where it encounter first space). Also, how to move them into 2 column format like filename, string found to an excel file.

Thank you.

You are putting a question update is an answer response that you are saying does not work for you. So, move this back to your original post, so that it can be followed better. All that being said, see my suggestion for you. — postanote, Sep 18 '22 at 01:22

score 0 · Answer 2 · answered Sep 18 '22 at 01:22

As per my comment.

### Split DB query string to CSV

Clear-Host
@'
File#1 = Create table xyz as select * from abc.bcd;
File#2 = Create table sdf as select * from asd.fgh;
'@ | 
ForEach-Object {
    $PSitem -replace '(=.*from\s|;)' | 
    ConvertFrom-Csv -Header FileName, Table -Delimiter ' '
}
# Results
<#
FileName Table  
-------- -----  
File#1   abc.bcd
File#2   asd.fgh
#>

How can I extract all databasename.tablename found in all .sql queries in a folder using Powershell?

2 Answers2