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.
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.