SQL script using SQLCMD for different physical servers

Question

I am just trying to script some basic stuff using SQLCMD from query editor.

I wanted to execute the same sql statements but in different sql servers and want to see the output. This has nothing to do with performance but the idea is to just check the data in different servers that we migrated. I just learnt SQLCMD would be very helpful here but wonder how to script this. Just assume same db and tables exist in all servers. So far, I do the below to get what I need and I am sure this could be done a lot more nicely !

:Connect Server1
SELECT count (*) FROM [DB].[dbo].[Table1]
SELECT count (*) FROM [DB].[dbo].[Table2]

:Connect Server2
SELECT count (*) FROM [DB].[dbo].[Table1]
SELECT count (*) FROM [DB].[dbo].[Table2]

:Connect Server3
SELECT count (*) FROM [DB].[dbo].[Table1]
SELECT count (*) FROM [DB].[dbo].[Table2]

Thank you !

Answer 1

There is a project called SchemaCrawler that is useful for comparing 2 different databases. I scripted it using batch files and it worked well.

But, the best way to do MSSQL queries from batch is like this. This demonstrates use of caret character for line continuations, delayed expansion of variables, output of SQL results to a file, and reading in variables from a file:

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
:: batch file for sql query
SET HOST=qa071.xyz.com
SET FIELDVAL=PRE%%
SET DBNAME=MYDB
SET USER=username
SET PASS=Passw3rd1

:: Do query
SET SQLSTRING=SELECT PARM_ID, PARM_NAME, PARM_VALUE^
 FROM CONFIG^
 WHERE PARM_NAME LIKE '%FIELDVAL%'
ECHO !SQLSTRING!
ECHO.
ECHO sqlcmd.exe -b -S !HOST! -d !DBNAME! -Q "!SQLSTRING!" -W -U !USER! -P !PASS!
sqlcmd.exe -b -S !HOST! -d !DBNAME! -Q "!SQLSTRING!" -W -U !USER! -P !PASS!

:: Count results and display count
SET SQLSTRING=SELECT COUNT(*)^
 FROM TB_CONFIG^
 WHERE PARM_NAME LIKE '%FIELDVAL%'
ECHO !SQLSTRING!
ECHO.
ECHO sqlcmd.exe -b -S !HOST! -d !DBNAME! -Q "!SQLSTRING!" -W -U !USER! -P !PASS! -h -1
sqlcmd.exe -b -S !HOST! -d !DBNAME! -Q "!SQLSTRING!" -W -U !USER!^
 -P !PASS! -h -1> count.txt
SET /P THECOUNT=<count.txt
ECHO The count is: %THECOUNT%
ECHO.
DEL /Q count.txt

ECHO Query is done. Hit any key to close this window....
pause>nul

Answer 2

That solution is probably the best if you don't want to use a batch file

If you are willing to add a batch file to the mix, you can do something like what is shown here Batch File

You can do this inside SSMS by creating a Server Group. MSDN