How to find all SQL Server instances on your network?

How do you discover all of the different SQL Server instances on your network?  We'd like to think we know our inventory, and are managing it well, but... Different developers, different software installations, or just different days of the week.  :-)  There are often new instances popping up on the network.  I believe this is called 'SQL Server Sprawl', where large numbers of SQL Server instances are installed in the domain, many of which are not needed, not managed... and very often not licensed. But that's another story.

Anyway, how do you discover all of the different SQL Server instances on the network?  SQL Ping, the MSFT Assessment and Planning Toolkit, Powershell, OSQL, and many more.  I'm wiling to bet this post is one of the fastest and easiest ways to do the deed.

Open a CMD prompt, maneuver to whatever directory you choose, and type the following:

         SQLCMD -L >SQLservers.txt

         Press ENTER.

That's it.  Seconds later (depending on how big your network is), you've output all discovered SQL Server instances on your network to the targeted .txt file.  

Seriously. 

It's that easy.




Short and sweet... leaving you all kinds of time to cleanup that SQL Server Sprawl.

Take a look at this for more details on the sqlcmd utility:
    https://msdn.microsoft.com/en-us/library/ms162773.aspx

Get Quarterly or Annual Sales with SQL Server PIVOT

A customer of mine handles transactions in their database for jewelry purchases, and today I was asked 'is there a way to know how many sales we've done each quarter, or year?'  Why yes, there is!  This is a very quick post showing a couple different ways to summarize sales or transactions, using SQL Server's PIVOT operator.  

This first example is 'static', where I have to tell SQL which values I am looking for.  In this case, the quarters for which I want the sale counts:

     SELECT

         [Year],

         [1] Qtr1,

         [2] Qtr2,

         [3] Qtr3,

         [4] Qtr4

     FROM (

           SELECT YEAR([PostingDate]) [Year],

           DATEPART(QUARTER, [PostingDate]) [Quarter]

           FROM dbo.YourTable

        ) SourceTable

     PIVOT(COUNT([Quarter])

     FOR [Quarter] IN ([1], [2], [3], [4])) PivotTable

As you can see, our rows come back as columns for each of the quarters in my query.  It's 'static' because this requires you to manually input the values you wish to query.  All very nice, but what if you don't know exactly what's in the table?

This next example does not require you to know, or input your values ahead of time.  In this case, the annual transactions, but I don't know exactly which years of data are in the table.  This is a 'dynamic' pivot:

     DECLARE

          @pivotquery NVARCHAR(MAX),

          @column NVARCHAR(MAX)

     SELECT YEAR([Posting Date]) [Year], COUNT(1) [Transaction Count] 

     INTO #Transactions

     FROM dbo.YourTable WITH (NOLOCK) ---- NOLOCK maybe necessary 

     GROUP BY YEAR([Posting Date])

     -- distinct values for pivot colunn

     SELECT @column = ISNULL(@column + ',','') + QUOTENAME([Year])

     FROM (SELECT DISTINCT [Year] FROM #Transactions) Years

     -- now collect the @columns via dynamic sql

     SET @pivotquery = 'SELECT ' + @column + '

                        FROM #transactions

                        PIVOT(SUM( [Transaction Count]   )

                        FOR [Year] IN (' + @column + ')) AS PVTTable'

     -- execute your query

     EXEC sp_executesql @pivotquery

In many cases, I think that 2nd approach may be more useful, because you don't have to know your queried values ahead of time.

Try it out yourself and let me know what you think!  I believe you should also take a look a this for a little more information regarding the use of PIVOT:

       https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx