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

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.

CodePudding user response：

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.

CodePudding user response：

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
#>