SSMS discard results after execution / sqlcmd clear screen / sqlcmd cls

Today I was looking around for a way to repeatedly execute a query after a 5 minute delay.

For my example I’ll simply use a GETDATE() call.  In SSMS this is simple enough; use a waitfor delay and a GO with a number after it:

WAITFOR delay '00:05:00'
SELECT Getdate()
go 12 

This will repeat the query every 5 minutes for an hour.  The issue I started to have is that I’m actually running a query with a larger result set and SSMS does not clear the results window.  Instead each result is printed below the previous.

As close as googling around for a way to clear the results before execution got me was, “Display results in a separate tab” and “Discard results after execution”.  Not exactly what I was looking for.

REF: https://msdn.microsoft.com/en-us/library/ms190078.aspx

I then started thinking maybe I could use sqlcmd and a cls somehow.  The closest sqlcmd statement I saw was “:RESET” which is used to, “clear the statement cache”.   Again, not what I was looking for

REF: https://msdn.microsoft.com/en-us/library/ms180944.aspx

Enter PowerShell to the rescue. (Realistically you could use a batch file too.)

I was able to wrap a sqlcmd call in a powershell do while loop and utilize “Start-Sleep” and the cls command to accomplish my goal:

$a = 1
DO
{
cls
start  -sleep -s 300
sqlcmd -s "127.0.0.1,1433" -d "master" -q "SELECT Getdate()"
$a     ++
} WHILE ($a -le 12)

For my real query my sqlcmd statement looked more like this:

sqlcmd -s "127.0.0.1,1433" -d "master" -i ".\test.sql" -Y20

A couple of items of note. 

1. When calling sqlcmd from powershell, use double quotes.
2. The –Y switch is very helpful for formatting. 
   (You can accomplish a tab delimiter with the `t escape sequence, but I found –Y more useful).