SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

PowerShell

PowerShell

Quick Blog: Grabbing basic machine info with PowerShell

Nov 29th, 2011
by Aaron Nelson.
2 comments

The other day I needed to track down how much RAM a couple of our servers had installed.  A few days later I needed to verify that a couple of them were in fact 64-bit and not 32-bit.  I decided I wanted to be able to get at this basic info any time that I wanted without having to remember all the syntax so I built it into a PowerShell function.

Building a PowerShell function is almost as easy easier than building a stored procedure around a select statement in SQL.  The reason PowerShell is easier than SQL is that when you have a parameter that you are passing in, you can give it a data type, but you don’t have to.

For the function below I gave it a default value of the local machine but you can pass in a machine name that you are trying to get to.

Just copy the code below into an ISE window and hit F5.

function Get-MachineInfo($ServerName="localhost")
{
get-wmiobject win32_computersystem -ComputerName $ServerName |
select DNSHostName, Manufacturer, Model, SystemType ,
        @{Name="TotalPhysicalMemoryInMB";Expression={"{0:n2}" -f($_.TotalPhysicalMemory/1mb)}},
        NumberOfLogicalProcessors, NumberOfProcessors, CurrentTimeZone, DaylightInEffect
}# End Get-MachineInfo

After you’ve done that, to call the stored proc function simply type in the name ( Get-MachineInfo ) to the prompt at the bottom and hit enter

image

My First MSSQLTip!

Nov 2nd, 2011
by Aaron Nelson.
2 comments

imageToday my first ever MSSQLTip was published.  Before you ask: Yes, it talked about PowerShell.

It came about because some of the SQL MVPs were trying to figure out the best way to download a VM that had been split up into 36 different equal-sized files.  I’m sure there’s a better way than this but at the same time it took me about 4 minutes to write.  Write is the wrong term, more like copy/paste/change a few things.

Give it a whirl and let me know what you think!  Smile

PowerShell takes the Pole at SQLRally!

May 10th, 2011
by Aaron Nelson.
Comments are off for this post

PowerShell takes the Pole at SQLRally!

PowerShell has taken the Pole for the DBA division at SQLRally!  Come see why the organizers have chosen this session to lead the pack to the Green Flag.

We will be covering new ground, not rehashing last year’s PASS Summit presentation.  I have developed several new examples for leveraging PowerShell in your everyday DBA role since then.  I am promising 6 new scripts for everyone but if I get on a role that number might be closer to 10 when they drop the green flag on this year’s event.  :-)

If you’ve never used PowerShell before don’t worry. We will spend a few minutes on a ‘get up to speed lap’ so everyone is going at the same pace when we step on the gas with all this new material.  All scripts will be made available for you to download during the session and access later on from pit-row.

After my session here’s what I’ll be checking out:

 

What sessions I'll be checking out during SQLRally

What sessions I'll be checking out during SQLRally

PowerShell Week at SQL University – Post 7

Apr 25th, 2011
by Aaron Nelson.
Comments are off for this post

Deploying Code

Deploying SQL code with PowerShell can be very easy but why would you want to do that instead of just open a script in SQL Management Studio and run it?  The number 1 reason that I can think of is when you need to deploy to multiple destinations.  Let’s get started with something simple, we’ll deploy a table that I use in the post on collecting database sizes.

Invoke-Sqlcmd -ServerInstance Win7Netbook -Database CentralInfo -InputFile C:\temp\dbo.DatabaseFileSizes.SQL

That’s a pretty simple example and one that doesn’t offer too much any benefit over just opening up SSMS and running the script.   Let’s take it a little further with the loop construct and this time we’ll deploy Adam Machanic’s ( blog | twitter ) sp_WhoIsActive to a bunch of machines

$DeployFile = C:\temp\who_is_active_v11_00.sql
<# Loops through Registered SQL Severs and applys WhoIsActive #>
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development\ | where {$_.Mode -ne "d"} )
{
"Deploying to "+ $RegisteredSQLs.ServerName;
Invoke-Sqlcmd -InputFile $DeployFile -ServerInstance $RegisteredSQLs.ServerName -database master}

So now we can see how we can deploy a SQL Script to multiple machines with just 4 lines of PowerShell.  The script obviously doesn’t have to be deploying code.  It could inserting/updating data or even verifying permissions.

PowerShell Interfaces with Other Hammers

Feb 8th, 2011
by Aaron Nelson.
5 comments

This month’s installment of T-SQL Tuesday is hosted by Pat Wright (blog | twitter). Pat says: “So the topic I have chosen for this month is Automation! It can be Automation with T-SQL or with PowerShell or a mix of both. Give us your best tips/tricks and ideas for making our lives easier through Automation.”

I have to tell you, this doesn’t get old:

imageI love that somebody on the other side of the planet is telling someone else that I ‘probably have a PowerShell script to solve their problem’. The funny thing is that a lot of the time I don’t have a script for the problem they’re trying to solve *yet*. I love it when someone gets referred to me and I don’t have a script because it gives me a chance to go explore the language and figure out something I don’t know. PowerShell makes this quest pretty fast.

imageIronically Paul White ( blog | twitter ) didn’t know I had a script for that, he was just making a joke. Well, actually, I didn’t have a script for that. I did however know right where to find the person that did; and that is even better than if I had a script of my own. Why is it better that I had to point someone some where else? Could I not have figured this script out own my own? No that’s not it at all.

imagePowerShell is NOT a hammer it’s an Automation Language. Better yet PowerShell is a Toolset, and it happens to come with some pretty freaking awesome hammers built in. We all understand that SQL Server isn’t just a hammer, that it’s an entire toolset. Inside of that toolset is: T-SQL, SQL Agent, Profiler, SSIS, the SMO, SSRS, SSAS etc… just to name a few.

The cool thing about PowerShell is it’s actually built to work with other hammers. So the reason that you hear me talking about PowerShell all the time is not because I’m replacing my T-SQL Hammer, I’m just automating it! Like this, here’s a quick script to figure out how long your SQL 2008+ instances have been running:

<# Number of Days since the Instance has been restarted #>
$InstanceList = "WIN7NetBook", "WIN7NetBook\R2", "WIN7NetBook\SQLExpress"
foreach($Instance in $InstanceList)
{
Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS 'ServerName'
, DATEDIFF(D, Sqlserver_start_time, SYSDATETIME()) AS 'NumberOfDays'
  FROM sys.dm_os_sys_info" -ServerInstance $Instance -Database master
}

The automation doesn’t stop with SQL Server. You can find plenty of posts form people in our SQL Community on working with things like Red-Gate’s SQL Compare, Outlook, Active Directory, and Subversion just to name a few. The key here is that all of these people are automating something adjacent to their SQL world and they’re using PowerShell to make it happen.

Heck just last week I wanted to know how many sessions had been submitted to SQL Saturday #67 in Chicago. Do you think I went to the schedule page, copied the list of sessions into Excel and then checked to see what line number the last session was on to get my count?? Heck NO!! Last year I wrote a script for Allen Kinsel ( blog | twitter ) to count how many people at the PASS Summit had registered their twitter handle when the signed up. I took that script, changed the link, changed the term I was looking for and presto! I now know that 105 sessions have been submitted to SQL Saturday #67.

That has nothing to do with SQL Server right?!… Except for that whole bit about it being a SQL Saturday that I was interested in.

<#Start up a web client and download the web page into a text file#>
$url="http://www.sqlsaturday.com/67/schedule.aspx"
$file="c:\temp\DownloadSession$(((Get-Date).ToString("yyyyMMddHHmm"))).txt"
$webclient = New-Object system.net.webclient
$webclient.DownloadFile($url,$file)            

<#Sift through the file for lines that include "viewsession" #>
(Select-String -path $file -Pattern "viewsession").count

OK, so what’s your point?

My point is that I wouldn’t have even had the twitter script that I started with if Allen Kinsel hadn’t taken a chance and sent me an email at noon the Friday before the PASS Summit and asked me if I could use PowerShell to count the number of attendees with twitter handles.

Again, what’s your point?

My point is Use PowerShell to make one tool talk to another.

And…

And tell people faced with an annoying problem that you think I’ve got a script for that because whose knows, maybe I do!

Recursive Find and Replace Your SQL Files with PowerShell

Feb 2nd, 2011
by Aaron Nelson.
Comments are off for this post

This is just a quick blog to help out with something I saw discussed on twitter yesterday.

When I do my presentations I have a set of scripts that have the name of the computer and instance I am working with.  I’m not a fan of using localhost or anything like that; I also like to use instance names that let people know what version of SQL that I’m using.  When I switch computers I spend less than a minute changing all of the names.  At the PASS Summit I used a laptop that I had just purchased; here’s the script I used to rename everything.

foreach ($SC in dir "$home\Documents\PoSh\" -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
(Get-Content $SC) | Foreach-Object { $_ -replace 'KILIMANJARO', 'R2' } | Set-Content $SC
}

To make this work for you just change out the highlighted parts above to whatever you need.  The part that says “$home\Documents\PoSh\” will go to the PoSh folder under your “My Documents” directory (If you don’t have one, now’s a good time Smile ).  You can also use a path like C:\SQL\Databases\AdventureQuirks\ here.  The -replace ‘KILIMANJARO’, ‘R2′ portion seems pretty self explanatory; same goes for -recurse.

If you just wanted to search for all the files with a certain table name, column name, stored proc name, etc… and return a report (not to modify) you can use something like this:

#Make sure to navigate to the directory that you want to start looking in:
## 
cd c:\temp            

foreach ($SC in dir -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}


This piece of code is setup slightly different solely to demonstrate another way you can use this functionality.  Make sure that you navigate to directory that you want to search in first for this script.  When you get the results you may end up seeing the same filename listed more that once because the “pattern” appears in the file multiple times.  To make the results show each filename just once simple add -List.


One final option that I want to call out is -Filter.  If you want to search for only .SQL files in a directory (or .PS1, .txt, whatever) just add this: -Filter *.SQL


So you might end up using something like this:


foreach ($SC in dir C:\SQL\Databases\AdventureQuirks\ -recurse -Filter *.SQL | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}


Alright I better stop here before I start showing off some other features that I just learned.

Hope that helps!

			

		

		
				
		

			
			
PowerShell Week at SQL University – Post 6

			
			

				Jan 20th, 2011
				
by Aaron Nelson. 

				1 comment							

			
			

								
Yesterday we went over some loop constructs to get information out of SQL Server.  While getting the information out is great, as Data Professionals our very next concern is going to be: “ok, where do I put this so that I can refer to it later?”


Today we’re going to talk about formatting and storing our results.  Our storage mechanisms are going to be a simple flat file, CSV, and the obvious one, a table.  This post is a little long but it walks you through how to overcome some of the frustrations when I started working with PowerShell over a year ago.  Enough preamble, let’s get started!


Output on the screen:


Start from the beginning by running this little command:


Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBookNow depending on how wide your PowerShell window is open to right now PowerShell will format the data to spool out in the results pain the way it thinks is best.  It may have come out ‘one row at a time’:


Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBook | Format-ListOr it may have come out like this:


Invoke-Sqlcmd -Query ‘SELECT * FROM sysfiles’ -Database MyBigFreakinDatabase -ServerInstance Win7NetBook | Format-Table” so you’ll probably want to get into the habit of putting a “ | Format-Table”  after the end of you queries.  Or if you’re lazy like me:  | FT.


When we want to run this cmd (or whatever query you really want to run) against multiple databases we’re going to want to know what database that the data came out of.  We might write something like this


foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{
Invoke-Sqlcmd -Query ' SELECT * FROM sysfiles'  -ServerInstance  Win7NetBook -Database $db.name | Format-Table
}


But the problem with that is We don’t know for sure which database each file came out of.  I mean if we have logical naming we can guess but that’s all it is, a guess.  What we can do is add our little “$db.name” that we’re using to pass in the name of our database.


foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-Sqlcmd -Query ' SELECT * FROM sysfiles'  -ServerInstance  Win7NetBook -Database $db.name | Format-Table
}


Object Sidebar: Probably the most important thing for SQL people to know about this language!


Believer it or not we’re using object oriented code right now.  In this example we’re pumping a list of databases as objects into a variable and then iterating over that list.  If we examine our object by piping it over to Get-Member “$db | Get-Member” we’ll see that it only has one property but if we had selected more columns it would have had more properties.  Typically objects have more than one property so that’s why we need to add the .name to our $db variable so that we only get the name property.


Outputting to a text file:


In the PowerShell language to output to a text file we can just use “>” and then give it a filename.  Since we’re inside of a loop we would just keep overwriting that file until we got down to the last database and all that would be in the file would be the info from that last database.  We can also use “>>” which allows us to append to a file like this:  >> C:\temp\MyDatabaseFiles_20110120.txt


Side Note: In addition to the “>>” we could have also used Out-File –FilePath C:\temp\MyDatabaseFiles_20110120.txt –Append with much the same results.  To make a long story short, use Out-File instead of >> whenever possible.


foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-Sqlcmd -ServerInstance  Win7NetBook -Database $db.name -Query ' SELECT * FROM sysfiles' | Format-Table >> C:\temp\MyDatabaseFiles_20110120.txt
}


That’s not what we expected was it?


There are two main problems with the output when we look at  MyDatabaseFiles_20110120.txt.  First, those database names are missing.  They’re missing because they weren’t in the pipeline in the first place.  Each time that we looped we were executing two different statements not just one; and the database name was only present in the first.  We could fix that by making the first line inside of the loop look like this $db.name >> C:\temp\MyDatabaseFiles_20110120.txt; but we won’t, at least not this time.


The second problem is that unless we have the editor open on a really, really wide monitor the output was likely truncated with some “…” at the end.  Now if we changed the output mode Format-List we might avoid that truncation problem but as database professionals “might” rarely cuts it.


Using Export-CSV to save off our results:


Funny story about Export-CSV later…


We’ll fire up a new variable called $MyResults and push our results into it each time that we pass through the loop (sorta~like a temp table but don’t make too strong of a connection to that).  After we complete the loop we will take the results and pass them down the pipeline to the Export-CSV cmdlet.  When we open that file we’ll see nice clean data output that we can save.


foreach ($db in Invoke-Sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
$MyResults += Invoke-Sqlcmd -Query " SELECT DB_NAME(db_id()) AS 'DatabaseName', * FROM sysfiles"  -ServerInstance  Win7NetBook -Database $db.name
}            

$MyResults | Export-CSV -Path C:\temp\MyDatabaseFiles_20110120.csv -NoTypeInformation


Output to a table:


I’ve talked about how to save rows to a table a couple of times before.  Do you remember that Wait Stats survey that Paul Randal ( blog | twitter ) did a little while ago?  Well, we have several hundred SQL instances where I work and I wanted to be able to send Paul results from a large number of those servers. The thing is I didn’t want to have to actually login to each instance, run the query, save off the results, then repeat over and over again.  So I came up with this post.  You can do the same to gather info multiple instances/databases in your environment.  It’s pretty straight-forward and very similar to the CSV example above but with a small pair of changes.  We need the two scripts that I mentioned in the other posts:  Chad Miller’s (Blog|Twitter)  invoke-sqlcmd2 and Write-DataTable functions.  Once we have those loaded (and hopefully their in our profile by now Winking smile) we’re off to the races being able to write information from multiple databases back into a single database.


foreach ($db in invoke-sqlcmd -query " SELECT name FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook)
{
$dt=invoke-sqlcmd2 -Query " SELECT DB_NAME(db_id()) AS 'DatabaseName', * FROM sysfiles" -ServerInstance  Win7NetBook -Database $db.name -As 'DataTable'
Write-DataTable -ServerInstance Win7NetBook -Database CentralInfo -TableName dbFileName -Data $dt
}


Here’s the SQL code to create that table so that you can test it out:


CREATE TABLE [dbo].[dbFileName](

[DatabaseName] [nvarchar](128) NULL,

[fileid] [smallint] NULL,

[groupid] [smallint] NULL,

[size] [int] NOT NULL,

[maxsize] [int] NOT NULL,

[growth] [int] NOT NULL,

[status] [int] NULL,

[perf] [int] NULL,

[name] [sysname] NOT NULL,

[filename] [nvarchar](260) NOT NULL

) ON [PRIMARY]


Output to Email:


Call me lazy but…


I put this next script together just to see if it could be done.  Going back to that Wait Stats Survey, if you read all the way to the end you’ll see I managed to get the collection process down to 3 lines.  The only thing is that you still had to do something to the result set to exclude your instance names for security, and then after that you still had to do the whole copy-paste thing.


To combat all that wasted time I came up with this script which will go ahead and enumerate the instances for you.  That way if for any reason Paul emailed you back and said ‘Holy Crap, how the hell did you manage to get that as your highest wait on Instnce #14?’ you’d still be able to go back and figure out which server he was talking about.  Also, If you would still have the results around in case you wanted to be proactive and take a look yourself (but let’s not get too carried away).


foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode -ne "d"} )
{
$dt=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -InputFile ./PaulAndGlensWaitQuery.sql -As 'DataTable'
Write-DataTable -ServerInstance "Win7NetBook" -Database CentralInfo -TableName TopWaitTypes -Data $dt
}            

$MultipleResults = Invoke-Sqlcmd2 -ServerInstance "Win7NetBook" -Database CentralInfo -Query "SELECT DENSE_RANK() OVER (ORDER BY [InstanceName]) AS 'SQLInstance' ,[WaitType] ,[Wait_S] ,[Resource_S] ,[Signal_S] ,[WaitCount] ,[Percentage] FROM [CentralInfo].[dbo].[TopWaitTypes] ORDER BY SQLInstance, Percentage" | ConvertTo-Html -Property SQLInstance, WaitType, Wait_S, Resource_S, Signal_S, WaitCount, Percentage | Out-String;
Send-MailMessage -To paul@SQLskills.com -Subject "Wait Stats Query Results" –From YourEmailAddress@GoesHere.com -SmtpServer smtp.SQLvariant.com -Body $MultipleResults -BodyAsHtml


Finally, you know we could put that SELECT statement into a .sql file.  That would cut it back down to ~6 lines.  We might even be able to wrap the email portion into something a little smaller but considering all that this script does I think this is good enough.


It’s Later


When I first tried to do something like this I was was shocked to find out that there is no –Append switch for Export-CSV.  I wasn’t very happy about that, I mean I couldn’t wrap my head around how you could leave something like that out.  This ranked right up there with finding out Utility Control Point only worked for managing other SQL 2008 R2 instances.  Luckily my story ended much better Tom’s.  First I reached out to ScriptingGuys ( blog | twitter ) to make sure that I wasn’t crazy.  Then I did all I could do, sulked and filed a Connect Item.  The following day I woke up to the sun was shining, the birds chirping, and a tweet from Dmitry freaking Sotnikov himself ( blog | twitter ) telling me that he had gone ahead and fixed that for me by building a proxy command.  (It’s a function and since functions out rank cmdlets in call order anyone can augment the PowerShell language by adding a proxy command.)


…  and THAT is why I love PowerShell.


Homework


Use the 3 techniques we walked through to run sp_configure against as many instances as possible.  Save it to a text file, CSV, and a table.  You might even want to add a date column or something.  You could also try email the results to yourself.


Do Not spam Paul Randal!  I cleared the email script with Paul before I included it.  If you want to send Paul the wait stats results from your servers I recommend that you test it first by emailing it to yourself.  If it looks good you go ahead and send it but make sure to include your email address in the –From parameter.

			

		

		
				
		

			
			
PowerShell Week at SQL University – Post 5

			
			

				Jan 19th, 2011
				
by Aaron Nelson. 

				8 comments							

			
			

								
In the previous posts we’ve just been poking around with PowerShell and trying to make the examples something that actually means something to a SQL person whenever we ca.  There are quite a few more language constructs that we need to cover but we have enough info to start recouping the time we’ve already invested.  Now it’s time to do one of those tasks that I just love to do with PowerShell.  We’re going to loop.  And we’re going to loop in a way that’s far easier than in any part of the SQL language.


We’re going to cover 3 different sources for our loop that are the most common for DBAs to use: table, text file, Registered Server/Central Management Server.  After that we’re going to do a double loop and then it’s time for you to find something to do with these.


Text File


In this chunk of code we’re just going to read from a simple text file from our local hard drive and then loop through the instances in that file one at a time.  You put one instance on each line of the text file.  Don’t put it in quotes unless you’re using a non-standard port number.  This is the easiest method because each “row” that comes out of the text file only has one property.  We’ll find out why that’s important in the next example.  In the meantime setup your text file and test it by running just this: Get-Content C:\PowerShell\AllInstances.txt.


foreach ($Instance in Get-Content C:\PowerShell\AllInstances.txt)
{
$Instance;
Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query "SELECT  object_name ,
        counter_name ,
        instance_name ,
        cntr_value ,
        cntr_type
  FROM sys.dm_os_performance_counters"
}


 


Database Table/ Query


In this example we could be reading rows out of a table or running a more complex query to determine the list of databases that we want to run out query against.  This may actually seem easier to most database people and it is.  We’ve got a centrally located table and we can just look at it and know that we can change the first query to select a list of databases from somewhere else.  But there’s a really important thing to know if you swap out the query.   This approach is sending one usable property (column) to the foreach loop and it’s called “name”.  If you change the query and the column ends up being called database_name, you’re going to have to change $($db.name) to be called $($db.database_name).  Otherwise you’re going to loose a lot of hair and get really ticked after about 20 minutes like I did!




foreach ($db in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" -database master -serverinstance Win7NetBook )
{$db.name;
Invoke-sqlcmd -Query 'SELECT *
  FROM sys.dm_exec_procedure_stats' -ServerInstance  Win7NetBook -Database $($db.name)
}


 


Registered Servers/ Central Management Server


Before you get started looping through your Registered Servers you’ll need to run this: Import-Module Agent if you want to do this exact example.  What we’re looking for here is all the jobs that have failed in the last 3 days in our “QA” group of servers.  This example should be easy enough for everyone to tweak on their own.  If you get stuck just remember to do Get-Help -Full Set-AgentJobHistoryFilter

(Huge thanks to Chad Miller for helping me put together this demo so that it would be a fast one to run!)


$filter = Set-AgentJobHistoryFilter -startDate $(get-date).AddDays(-3) -endDate $(get-date) -outcome 'Failed'

foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
Get-AgentJobHistory $RegisteredSQLs.ServerName $filter | where-object {$_.StepID -ne 0}
}


 


Double Loop


This has to be one of my favorite PowerShell scripts of all time (so far).  I had to run a query against every database in a group of over 10 servers.  I’ve changed this one around a little but I’m sure you’ll find a use for it!  (Think permissions.)


foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\QA\ | where {$_.Mode -ne "d"} )
{
    foreach ($DBName in invoke-sqlcmd -query "SELECT name
  FROM sys.databases WHERE name in ('AdventureWorks',
            'AdventureWorks2008',
            'AdventureWorks2008R2',
            'AdventureWorksDW'
            ) " -database master -serverinstance $RegisteredSQLs.ServerName )
            {
                    invoke-sqlcmd -query 'SELECT *
        FROM sys.dm_db_index_usage_stats' -ServerInstance $RegisteredSQLs.ServerName -database $DBName.name
            } #EndOfTheFoundDatabasesLoop
} #EndOfTheRegisteredServerLoop


 


Homework


Try out each of these methods and think up something you could use this for.  If you hit on something that save you some clicking around in SQL Management Studio please mention it in the comments.

			

		

		
				
		
	

			← Earlier Posts