Identify the versions & editions of all SQL Instances in a company

What if you are assigned to a task of identifying the versions & editions of each and every SQL Instance in the company? Your job has just been made easier with the script below! Run the below script on a CMS (Central Management Server) and you should have a neatly formatted result set.

set nocount on
declare @version varchar (10)
declare @servername varchar(250)
declare @ver varchar(10)
set @servername=(select @@servername)
set @version = substring(cast(SERVERPROPERTY('productversion') as varchar(100)),0,CHARINDEX('.',(cast(SERVERPROPERTY('productversion') as varchar(100))))+2)
set @ver=case @version
       when '8.0' then '2000'
       when '9.0' then '2005'
       when '10.0' then '2008'
       when '10.5' then '2008 R2'
       when '11.0' then '2012'
       when '12.0' then '2014'
       when '13.0' then '2016'
       end
select SERVERPROPERTY('MachineName') as Physical_Server_Name, @ver as Version, SERVERPROPERTY('productversion') as Build_Number,
SERVERPROPERTY('Edition') as Edition
set nocount off

Windows batch file to parse thru server names in a text file and execute scripts against them

DBA's often need to execute the same set of scripts against multiple instances. The job is made a lot easier by using a windows batch file that executes a sqlcmd against all the instances one after the other. All the server names can be placed in a text file and the below code parses thru the text file one after the other and executes sqlcmd against them. The output of each sqlcmd command is logged to the file 'output.txt'. '>>' is the operator that appends the output to the same text file each time sqlcmd runs rather than creating a new file and over-writing it every time.


@echo off
for /F "tokens=*" %%a in (servernames.txt) do sqlcmd -S%%a -E -i "C:\check\script.sql" >> "C:\check\output.txt" -b