Few days ago I had the time to update one of my too called SQL_Spring_Cleaning.
This made me figure out that there was space for another tool: I called it Wheke-Wheke , to honour a Māori song that I still like to sing with my daughter.
Sometimes you find yourself alone in a Data Center with a serious task: the Data Center has been growing in size in the last two decades and you need to discover what databases are in use and what not.
This is when you might need the help of Wheke-Wheke which tentacles will allows you to discover unused SQL Server databases across the whole Data Center.
How it works
I know what you are thinking:
“oh, well, you need to use Linked Servers for that, right?”
No. Linked Servers are not needed.
“Well, so you are creating a Central Management Server?”
No. Who the hell uses that…
“So you are deploying a script on each server?”
Naaa…, your Project Manager will come to your desk with a stick.
“You are using PowerShell?”
“So what are we using here… ?”
We are using OPENDATASOURCE one of the most underrated T-SQL arguments that will silently allows you to connect to a remote SQL Server without need to create a permanent connection to it. We will then use
FETCH to iterate through a list of SQL Server instances.
The most important part of the code is this:
DECLARE @ServerName VARCHAR(50), @DynamicSQL NVARCHAR(MAX) DECLARE @myTableVariable TABLE (id INT, ServerName VARCHAR(50)) INSERT INTO @myTableVariable VALUES (1, 'Server_A'), (2, 'Server_B') -- Add your SQL Server list here DECLARE VarCursor CURSOR FOR SELECT ServerName FROM @myTableVariable OPEN VarCursor FETCH NEXT FROM VarCursor INTO @ServerName WHILE @@FETCH_STATUS = 0 BEGIN SET @DynamicSQL = ' --INSERT INTO [msdb].[dbo].[ConnectionCounts_Remote] SELECT ''' + @ServerName + ''' AS [ServerName],* FROM OPENDATASOURCE(''MSOLEDBSQL'', ''Server=' + @ServerName + ';TrustServerCertificate=Yes;Trusted_Connection=Yes;'').master.sys.databases' EXEC (@DynamicSQL) FETCH NEXT FROM VarCursor INTO @ServerName END CLOSE VarCursor DEALLOCATE VarCursor
As you can see we are going to create a list of SQL Server instances and query each of them.
In this example I’m querying
sys.databases but you can query any other table.
The result is spectacularly fast, noiseless and needs zero code to be deployed.
I’m going to work more on it as this query is quite nice and might allow you to retrieve all sort of data. The fact that you can save the result in a #TempTable will allow to save the results with a right click to Excel and this is gold if you need to withdraw raw data.
So please, take the code and use it.