A PowerShell Script to Discover all SQL Servers on my Domain and SQL Service Pack Level

I need a PowerShell script to Discover all SQL servers on my domain, plus determine the SQL Service Pack Levels for each.

If anyone has a script that you are willing to share, it would be very helpful.  If anyone can assist in writing a script to achieve this objective I would greatly appreciate your assistance.

Thank you,

lipotechSys EngAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:

There are tools such as SQLping that can do the finding.

Or Dave Pinal writes that either osql or sqlcmd from the commandline can list instances on a server

I just tested sqlcmd -L from my commandline, and it found all the instances of SQL that I was aware of on my network.

lipotechSys EngAuthor Commented:

I tried to launch sqlcmd -L from the command line and received an internal command not recognized error.  Should this command be launched under some special condition?

lipotechSys EngAuthor Commented:
I am seeking a PowerShell solution to this problem.  Does anyone have a PowerShell script or recommendation on how a script to solve this problem should be constructed?

David ToddSenior Database AdministratorCommented:

What version of SQL tools are on your laptop? I have several versions of SQL installed on my desktop - 2008R2 - 2014.

Once you have the names of the SQL instances, then a simple query getting the serverproperties (https://msdn.microsoft.com/en-us/library/ms174396.aspx)

Look for Productversion and ProductLevel I also get collation.

Here is the body of my powershell script. Note I am getting a list of instances from a server.
#Main Body

$ss = Invoke-SqlCmd -Query "select FQDN + isnull( '\' + nullif( InstanceName, '' ), '' ) as ServerName, VersionNumber from _dba_logging.dbo.SQLServer where ( DateDecommissioned is null or DateDecommissioned > getdate()) and isSQL = 1" -ServerInstance myServer\myInstance

foreach( $s in $ss ) {

 write-output $s.ServerName
$d = Invoke-SqlCmd -Query "
	getdate() as LogDateTime
	, serverproperty( 'MachineName' ) as MachineName
	, serverproperty( 'ServerName' ) as ServerName
	, serverproperty( 'ProductVersion' ) as ProductVersion
	, serverproperty( 'ProductLevel' ) as ProductLevel
    , serverproperty( 'Collation' ) as ServerCollation
	, cast( serverproperty( 'Edition' ) as varchar( 50 ) ) as Edition
	, cast( sd.name as nvarchar( 128 ) ) as DatabaseName
	, sum( fi.size ) * 8 / 1024.0 / 1024.0 as DataSizeGB
	, sum( ti.size ) * 8 / 1024.0 / 1024.0 as LogSizeGB
    , databasepropertyex( sd.name, 'Collation' ) as DatabaseCollation          
from master.dbo.sysdatabases sd
left outer join (
		, sum( fii.size ) as size
	from master.dbo.sysaltfiles fii
		fii.groupid > 0 -- not logs
	group by
	) fi
	on fi.dbid = sd.dbid
left outer join (
		, sum( tii.size ) as size
	from master.dbo.sysaltfiles tii	
		tii.groupid = 0 -- logs
	group by
	) ti
	on ti.dbid = sd.dbid
group by
" -Database tempdb -ServerInstance $s.ServerName | foreach { 
        add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd HH:mm:ss")) -passThru
        } | select-object UsageDT, MachineName, ServerName, ProductVersion, ProductLevel, Edition,  DatabaseName, DataSizeGB, LogSizeGB, ServerCollation, DatabaseCollation | Out-DataTable

Write-DataTable -ServerInstance "myServer\myInstance" -Database _dba_logging -TableName LogDatabase -Data $d


Open in new window

Yes its done in powershell, but its more a SQL query.

The Out-DataTable and Write-DataTable functions I got off the net.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lipotechSys EngAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.