How to execute SQL files using T-SQL?

Sometimes ago I read an article on how to execute the sql file using powershell by Martin Bell. After reading it the question came into my mind is “Why not just use T-SQL? Would that be simpler? “. Indeed, powershell gives you a wide approach of doing the database activities, the same can be achieved using T-SQL as well. The below code snippet gives a overview of “How to execute sql using T-SQL”

DECLARE @fPath VARCHAR(4000) = 'C:\SQL\Script1.sql'
DECLARE @outPath VARCHAR(4000) = 'C:\SQL\Script_exec_log.txt'
DECLARE @serverPath VARCHAR(20) = 'localhost'
DECLARE @sqlString VARCHAR(4000)
DECLARE @dbList VARCHAR(1000), @dbName VARCHAR(100), @rowCount INT
SELECT @dbList=COALESCE(@dbList + ',', '')+ name FROM sys.databases WHERE name like '%Test'
SET @rowCount = @@rowcount WHILE 1= 1
SET @dbName = CASE WHEN (@rowCount = 1 OR @dbList != @dbName) THEN @dbList ELSE SUBSTRING(@dbList, 1, CHARINDEX(',', @dbList)-1) END
SET @sqlString = 'SQLCMD -E -S'+@serverPath+' -d'+@dbName+' -i'+@fPath +' -o'+ @outPath
PRINT @sqlString
EXEC master..xp_cmdshell @sqlString
IF @dbList= @dbName BREAK
SET @dbList = SUBSTRING(@dbList, CHARINDEX(',', @dbList)+1, LEN(@dbList))

2 thoughts on “How to execute SQL files using T-SQL?

  1. There are times I’ve had to do things similar to this, but only if I have to. Since PowerShell has come out in 2006, I have only had to do this infrequently. Powershell is easier to write for something like this and easier to reuse.

    It looks like this script currently only works with 2 databases and has errors if it has more or less than that. It appears to have problems with @dblist. I would suggest using a temp table and select from that, instead of trying to use a CSV in a variable.

    There are a few other problems with using this type of script
    – xp_cmdshell is disabled by default.
    – This is very hard to maintain/debug. i.e. if someone has a very large number of databases, it will not work on all databases.
    – You need to quote almost everything. The only thing you don’t need to quote is the server name. You need to quote the output path, the database name, and the path to the script. If any of those have a space in them, it will fail.
    – using the output for another process is very difficult. Since it is just text and not in a particular format, you would need to parse it to use it.

    Using PowerShell Scripts I have made over the years, the equivalent of this would be:

    Get-SQLDatabase -Server localhost -Database ‘Test*’ | % { Invoke-SQL -Server $_.Server -Database $_.Database -InputQueryFile ‘C:\SQL\Script1.sql’ } | Export-csv ‘C:\SQL\Script_exec.csv’

    This would give you a csv file you could utilize within Powershell with Import-CSV or open with Excel.

    • Hey Steve,
      Thanks for your input, I appreciate it.
      The point here is not about using T-SQL instead of Powershell? the thing I presented is we can achive the goal in either ways.

      Anyways, apart from this what you said about script this is the sample script anyone using it can modify the script as per the requirement.

      Thanks again for sharing your thoughts. and I would love to go for powershell , if i can do this stuff in just 2 lines 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s