Searching SQL Servers & Saving Results, with Write-SqlTableData

Just a quick blog today to show you something which is possible with a handful of lines of code. I am writing a more in-depth article for SQL Server Central on Write-SqlTableData, in the meantime hopefully this might inspire or help you with something you need to do.

The other day I was chatting with my friend Tom Roush ( B | T ) and he mentioned needing to scan every database on every SQL Server instance in his environment and look for a particular word in the stored procs. Tom and I had discussed basics of his environment once before so I knew he already had Registered Servers filled out with all the instances he was going to need to search.

Here’s the query Tom needed to run:

SELECT @@SERVERNAME AS 'Instance',
 DB_NAME() AS 'Database',
 so.Name AS 'ProcName',
 sc.text 
 FROM syscomments  sc
JOIN sysobjects so
 ON  sc.id = so.id
WHERE  sc.text LIKE '%baconbaconbacon%'

Setting up the table for the search results

Write-SqlTableData came out last month in SSMS 16.4, it’s V1 so it’s not perfect [yet] but boy is it ever magic when you’re in a hurry! In this case, in order to be able to save the results for Tom, we need to find what we’re looking for once and then create a table in SQL Server using those results. To do this we use our very much improved friend Invoke-Sqlcmd and we leverage the new -OutputAs DataTables parameter which came out earlier this year. That give us a .Net Data Table. Next we send the results of that query to Write-SqlTableData, which accepts .Net data tables like Buck Woody ( B | T ) accepts coffee, via pipeline! You will need to give it a name schema & table name to use, oh and which database you want this in. Since this table doesn’t exist yet, we use the -Force parameter and like magic Write-SqlTableData is going to create the table for us. We do all of this here, because when we loop through we don’t want to create a new table each time.

Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2014 -OutputAs DataTables -Query ”
SELECT @@SERVERNAME AS ‘Instance’,
DB_NAME() AS ‘Database’,
so.Name AS ‘ProcName’,
sc.text
FROM syscomments sc
JOIN sysobjects so
ON sc.id = so.id
WHERE sc.text LIKE ‘%baconbaconbacon%'”|            
Write-SqlTableData -ServerInstance localhost -DatabaseName sandbox -SchemaName dbo -TableName SysCommentSearchResults -Force

I <3 Registered Servers

I’ve shown how to leverage Registered Servers in PowerShell a couple times before (Exhibit A | Exhibit B). I love Registered Servers, so much that I have a snippet setup so that I can quickly run any PowerShell (or even just SQL) script against every SQL Server instance in my environment. I guess I should blog about that sometime. Until then, here is what I use, it should work for you too if you have any Registered Servers.

<# .DESCRIPTION #>            
foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:SQLRegistration’Database Engine Server Group’ | where {$_.Mode -ne ‘d’} )            
{            
$RegisteredSQLs.Name
}

The Final Product

To search all of the SQL Server instances in Tom’s environment all we need to do is marry up the two above pieces of code. I have highlighted in yellow the two pieces of code that needed to be changed to go from a single searching a single instance to searching multiple. It was also necessary to add a call to Get-SqlDatabase in order to get a list of databases on each instance, which is highlighted in blue.

<# .DESCRIPTION Loops through Registered Server to get a list of instances, then uses Get-SQLDatabase to find each database on those instances, then uses Invoke-SQLCmd to search syscomments in each of those databases. Finally, it writes the resulting rows it found to a table in SQL Server using Write-SqlTableData #>            
foreach ($RegisteredSQLs IN dir -Recurse SQLSERVER:SQLRegistration'Database Engine Server Group' | where {$_.Mode -ne 'd'} )            
{            
Get-SqlDatabase -ServerInstance $RegisteredSQLs.Name | %{            
Invoke-Sqlcmd -ServerInstance $RegisteredSQLs.Name -Database $_.Name -OutputAs DataTables -Query "
select @@SERVERNAME AS 'Instance', DB_NAME() AS 'Database', so.ProcName, sc.text
from syscomments sc
inner join sysobjects so
on sc.id = so.id
where sc.text like '%baconbaconbacon%'"|            
Write-SqlTableData -ServerInstance localhost -DatabaseName sandbox -SchemaName dbo -TableName SysCommentSearchResults            
}            
}

So there you have it. Is this code perfect? No. Did it take me less than 10 minutes to write and test this code? Yes. Will it get the job done so that Tom can back to what he was doing before he ran into this roadblock that he had to solve? Yes.

Maybe you don’t need precisely this, but hopefully it will inspire to play around with Write-SqlTableData for something that you do need to do!

Cheers!