Dynamic Query with sql file name

PowerShell Help
1

Hello,

I have n number of sql file in a folder like below.

dbo.sql1.sql

stg.sql2.sql

I want to create dynamic sql like:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = 'sql1' )
BEGIN

Table schema and name should come from the file name without.sql file.

I was trying something like below :

Get-ChildItem C:\Folder | foreach{$_.Name -replace ".sql",""}|
foreach{"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $_ AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$_' )
BEGIN"}

Here I am not able to generate schema Table schema at the runtime.

Once it’s done then I want to put this script in the .sql file before the create command.

Thanks,

2

Basically, you want SQL query to be created based on the file name
If the file name patterns are all same, then …

Get-ChildItem C:\Folder -File | ForEach-Object -Process {
$SplitFileName = $_.BaseName -split '\.'
"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $($SplitFileName[0]) AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$($SplitFileName[1])' )BEGIN" | Out-File -FilePath "c:\$($_.BaseName)-query.sql"
}

btwn,
I request you to use code formatting tags while posting code in the forum which makes other to easily understand your code, below link will help you.

3

Color me confused, as I am having a really hard time trying to get what you are after.
Bare with me, I’m old and halftimers kicks in, well, you know.

You are getting a bunch of files with this…

Get-ChildItem C:\Folder | foreach{$_.Name -replace ".sql",""}

Then passing those file names (dbo.sql1, stg.sql2) sequentially, to this …

foreach{"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $_ AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$_' ) BEGIN"}

… what exactly did you expect to happen?

In that second foreach, you are not passing any kind of executable command or statement, just a string with the filename.

Get-ChildItem D:\Temp\*.sql | 
foreach{$_.Name -replace ".sql",""}

# Results

dbo1
stg2

Why are you removing both .sql tokens again?
Or did you mean to do this?

#  Remove the file extension
Get-ChildItem D:\Temp\*.sql | 
foreach{$_.Basename}

# Results

dbo.sql1
stg.sql2

and them pass that adjusted filename to this string?

Get-ChildItem D:\Temp\*.sql | 
foreach{$_.Basename} |
foreach{"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= $_ AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = '$_' ) BEGIN"}

# Results

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= dbo.sql1 AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = 'dbo.sql1' ) BEGIN
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= stg.sql2 AND TABLE_TYPE = 'BASE TABLE'  AND TABLE_NAME = 'stg.sql2' ) BEGIN

The above is just passing a string without calling SQL to run it. Is that what you meant to do?

Then pass those to some other file to run as a SQL command, using either Out-File or redirects?

I am working under the assumption that these are different SQL instances, as per the .sql1 and .sql2

4

@Kvprasoon, Thanks for the reply now the dynamic query is working.

And in the second part I don’t want to create the new file but in the existing .sql file I have the table creation script and before the Create statement I want to append the newly created if exist command.

5

@Postanote Thanks for understanding my code, Actually I am Couple of days old in PowerShell. learn some basic and trying something. basically, I want to achieve what @Kvprasson replied.

 

 

6

@Joe - You can append to a file, “Append - not prepend”. So you can have logic to re create your existing query with multiple appends and yourself can do it. Just go through below documentation.

Get-Help Out-File -Full

Put on some logic and update when you encounter any problems, we are here to help you.