By Andy Cox

Hi everyone! Long time no talk.

So today I was trying to accomplish the following:

On an instance of SQL Server, there could exist a database with a naming convention similar to “%naplog%”. If a database with that naming convention exists, check to see if some tables, indexes and stored procedures exist; if they don’t, create them.

Simple enough, right?

Since there could be any number of SQL instances that might meet this qualification, I decided to automate the process. I debated for a few minutes on which method to use, and fought with SQLCMD for quite a while. After I decided that SQLCMD wouldn’t give me what I needed, I decided to try PowerShell.

Now, I have to admit: I’ve avoided PowerShell for a while now. Yes, guilty as charged. My thought has been that PowerShell seems cool, but it’s a better fit for the Active Directory and Windows Admins, not a SQL guy.

I now stand before you, a changed man.

After some Bing research ( as well as some helpful suggestions from my co-workers ), I strung together the following:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

$server = “andycox1\denalictp3″

$sqlserver = new-object “Microsoft.SqlServer.Management.Smo.Server” $server
foreach ($db in $sqlserver.Databases)
{
if ( $db.name -like “*naplog*” )
{
invoke-sqlcmd -inputfile create_db_objects.sql -database $db.name -ServerInstance $sqlserver.name -IgnoreProviderContext
}
}

To get things started, I loaded the SMO assembly ( “Microsoft.SQLServer.SMO” ); this allows me to create an instance of a SQL instance object ( $sqlserver ). I used a local variable name “$server” to specify the SQL instance I wanted to connect to.

Then, I looped through all of the databases on this instance ( “foreach( $db in $sqlserver.databases )” ) and looked for any databases that had a naming convention similar to a specific string ( “if ( $db.name -like “*naplog*” )” ). Once I find an instance of a database that meets my requirements, I call the invoke-sqlcmd cmdlet, passing parameters of the T-SQL script I want to execute along with the SQL instance and database I want to connect to.

I have to admit – I had some fun with this, and plan on exploring the mystery of PowerShell a bit more.

Check out these links for more references on PowerShell, and happy scripting!

Microsoft PowerShell – http://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx

Microsoft PowerShell Team - http://blogs.msdn.com/b/powershell/

Gary Siepser’s PowerShell and Exchange Blog - http://blogs.technet.com/b/gary/

Link to the original article: http://blogs.technet.com/b/andyc_sql/archive/2011/11/30/dipping-my-little-toe-into-the-powershell-waters.aspx