Retrieve a List of SQL Server Databases and their Properties using PowerShell

By:   |   Updated: 2009-05-26   |   Comments (41)   |   Related: > PowerShell


Problem

In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. I would like to translate some of the Transact-SQL scripts that I use every day, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.

Solution

One of the things that we do as DBAs is to retrieve a list of databases and their properties for auditing and reporting purposes. We check for properties such as recovery model, available free space, autoshrink, etc., and generate action items based on them. We've already seen how to access the Server object - its properties and methods - using SMO. We will dig into the object hierarchy and look at the different members of the Server object. A SQL Server instance can be described using different properties like instance name, logins, settings, all of which are members of the Server object.

What would be of main interest for this tip is the Databases property. This property represents a collection of database objects defined on a SQL Server instance, regardless whether they are system or user databases. Let's start by retrieving all the members of the Databases property. The script below is the same as the one in the previous scripts with the addition of a Get-Member cmdlet to retrieve the list of members for the Databases property, specifying Property as the MemberType which just means to retrieve all the database properties. Be sure not to get confused with the terms - the Databases property of the Server object is a collection of database objects and the database objects themselves have their own properties. 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000"
$dbs=$s.Databases
$dbs | Get-Member -MemberType Property
reflection assembly

From the list of properties from the Database object, let's select the most common ones - Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size and SpaceAvailable 

$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
collation

Notice that it is pretty simple and straight-forward. Since the $dbs variable contains a collection of database objects, you can now pipe the results so a Select-Object cmdlet, which we just refer to as SELECT. You can then make this script dynamic by introducing variables and saving it as a PowerShell script which we have done in previous tips.

Reading from a Text File

There are a couple of ways to list the servers that you manage, but for simplicity's sake, we'll use a simple text file. What we will do is store the names of the SQL Server instances that you administer in a text file named SQL_Servers.txt and pass the contents in a variable named $instance. We will use the Get-Content cmdlet to read thru the contents of the text file and the ForEach-Object cmdlet to iterate thru the collection. The script would look something like the one below. You can either write it in a script file or type it directly in the PowerShell console 

ForEach ($instance in Get-Content "D:\SQL_Servers.txt")
{
     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
     $dbs=$s.Databases       
     $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
}
partial name

Generate Fancy Reports - Enter Microsoft Excel

Let's admit it, Microsoft Excel happens to be the most popular application to generate spreadsheets and reports so it does make sense to make the most out of it with any reports that we need to generate. IT Managers and key stakeholders would like to see either graphs with colors instead of just numerical values within reports. What we will do is to use PowerShell to automate report creation for our database audit report with Excel. There are a few key things that we need to understand about the process. First, we will be calling Excel thru COM automation and not as a .NET application. We used the New-Object cmdlet to create an instance of the Excel object, the -ComObject parameter to create a new COM object of type Excel.Application which is the ProgID of the object we want to create and assign it the variable $Excel. 

$Excel = New-Object -ComObject Excel.Application

You might be interested to find out what other COM components are available on a Windows machine for you to use. To retrieve their ProgID values, you can query the registry with a one-liner like the one below.

dir  "REGISTRY::HKEY_CLASSES_ROOT\CLSID" -include PROGID -recurse | foreach {$_.GetValue("")}

So much for COM objects as we only need the Excel.Application at this point. Next, we set the Visible property of the Excel object to True so we can see what is happening 

$Excel.visible = $True

Next, we use the Add() method to add a new workbook to the instance of the Excel object and use the Item property to create an object reference to the first worksheet in the workbook. The variable $Sheet will hold that pointer to the worksheet and will be referred to in most of the code 

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

You can add as many workbooks as you want, but one would be enough for our purpose. Now, to actually work with Excel we need to reference row and column numbers. To do that, we call the Cells property of the Excel object to return the cell or cells in the active sheet. We can then modify the cells like providing a value, changing the Font properties, etc. An example to assign a value to a cell in row 1, column 1 and change the Font property to Bold is shown below. 

$Sheet.Cells.Item(1,1) = "SQL SERVER INSTANCE NAME:"
$Sheet.Cells.Item(1,1).Font.Bold = $True

Saving the code and running it in PowerShell will open up an Excel spreadsheet that will look something like the one below

excel

You can either call the SaveAs() method of the Worksheet object $Sheet to save the Excel spreadsheet or just leave it as it is.

Imagine dealing with row and column values that are dynamic since there would be a fair amount of databases on each of your SQL Server instances. Below is a PowerShell script that takes into account what we have just covered with SMO and Excel to create a fancy report 

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

     $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
     $Sheet.Cells.Item($intRow,2) = "COLLATION"
     $Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"
     $Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"
     $Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"
     $Sheet.Cells.Item($intRow,6) = "SIZE (MB)"
     $Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"

     #Format the column headers
     for ($col = 1; $col -le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }


     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell


     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

     #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

     #Formatting using Excel 

     ForEach ($db in $dbs) 
     {

          #Divide the value of SpaceAvailable by 1KB 
          $dbSpaceAvailable = $db.SpaceAvailable/1KB 

          #Format the results to a number with three decimal places 
          $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable 

          $Sheet.Cells.Item($intRow, 1) = $db.Name
          $Sheet.Cells.Item($intRow, 2) = $db.Collation
          $Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel

           #Change the background color of the Cell depending on the AutoShrink property value 
           if ($db.AutoShrink -eq "True")
          {
               $fgColor = 3
          }
          else
          {
               $fgColor = 0
          }

          $Sheet.Cells.Item($intRow, 4) = $db.AutoShrink 
          $Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

          $Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
          $Sheet.Cells.Item($intRow, 6) = "{0:N3}" -f $db.Size

          #Change the background color of the Cell depending on the SpaceAvailable property value 
          if ($dbSpaceAvailable -lt 1.00)
          {
               $fgColor = 3
          }
           else
          {
               $fgColor = 0
          }

          $Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable 
          $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

          $intRow ++

     }


$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

Running the script in PowerShell will generate an Excel spreadsheet similar to the one below, highlighting those properties that need immediate attention using some fancy colors from the definitions available here.

database name
Next Steps
  • Download and install Windows PowerShell
  • Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects
  • Find out more about the Excel 2003 Object Model to incorporate it in your automated report generation


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-05-26

Comments For This Article




Tuesday, November 13, 2018 - 9:16:32 AM - pregunton Back To Top (78227)

Using SMO with SQL Server Authentication, user and password ?

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

thx

Thursday, June 21, 2018 - 7:33:52 AM - bass_player Back To Top (76267)

You can filter the results of the $dbs variable to something like this:

$dbs | Where-Object {$_.Name -contains "any character in the dbName that you want to include"}

Refer to the documentation on using the Where-Object PowerShell cmdlet for additional information

https://docs.microsoft.com/en-us/powershell/scripting/getting-started/cookbooks/removing-objects-from-the-pipeline--where-object-?view=powershell-6

Wednesday, June 20, 2018 - 6:09:32 PM - David Waller Back To Top (76261)

 This is very interesting and want to start using it. How can I exclude databases I don't want?

 

Monday, April 3, 2017 - 4:23:37 PM - Matt Back To Top (54197)

 

 Script fails to query sql servers with instance names.

Sunday, January 1, 2017 - 6:02:25 PM - bass_player Back To Top (45075)

You can use the Export-Csv cmdlet after building an object that contains the information you need. The code before the section Generate Fancy Reports - Enter Microsoft Excel section is all that you need to work with

Tuesday, December 6, 2016 - 8:56:25 AM - Marvin Woods Back To Top (44906)

 

Good afternoon,

Thank you for this script! This will help me create monthly reports on the growth of our databases.

We need to import these reports into QlikView in CSV format. How should I go about editing your script so the export is done to CSV format?

 

Regards,

Thursday, July 14, 2016 - 8:38:11 AM - Disha Back To Top (41885)

HI

 

I want this output to be exported directly in a csv file along with the instance names. 

What should i do.

 

Friday, May 13, 2016 - 12:15:54 AM - Nandini De Back To Top (41474)

Hi,

I want to retrieve the logspace of every database for a list of servers and want to post the output to a different remotely accessed SQL Table.

I have tried below powershell script.

$instances= Get-Content "E:\Nandini_new\SQL_Servers.txt"

ForEach($instance in $instances)

{

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs=$s.Databases

foreach ($db in $dbs)

{

function

Do-InsertValue ([Data.SqlClient.SqlConnection] $OpenSQLConnection)

{ 

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

$sqlCommand.Connection = $sqlConnection

$sqlCommand.CommandText = "SET NOCOUNT ON; " + "insert into dbo.Logspace (ServerName, DatabaseName, LogName, TolatSpace, UsedSpace)" + "values (@instance, @dbname, @logName,@TotalSpace, @UsedSpace));          

 

#,

#

$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@instance",[Data.SQLDBType]::VarChar, 30))) | Out-Null

$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@dbname",[Data.SQLDBType]::VarChar, 30))) | Out-Null

$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@logName",[Data.SQLDBType]::VarChar, 30))) | Out-Null

$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@TotalSpace",[Data.SQLDBType]::float))) | Out-Null

$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@UsedSpace",[Data.SQLDBType]::float))) | Out-Null

#$dbname = $db.Name

#write-host $fg.files | select name

 

$logInfo = $db.LogFiles | Select Name, Size, UsedSpace

#if(($logInfo.UsedSpace * 100 / $logInfo.Size) -ge 80)

#{

# Here we set the values of the pre-existing parameters based on the $file iterator

$sqlCommand.Parameters[0].Value = $instance

$sqlCommand.Parameters[1].Value = $db.name

$sqlCommand.Parameters[2].Value = $logInfo.Name

$sqlCommand.Parameters[3].Value = ($logInfo.Size / 1000)

$sqlCommand.Parameters[4].Value = ($logInfo.UsedSpace * 100 / $logInfo.Size)

 

# }

}

$DBServer = "abcdefgh"

$DBName = "Automated"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"

$sqlConnection.Open()

# Quit if the SQL connection didn't open properly.

 

if ($sqlConnection.State -ne [Data.ConnectionState]::Open)

{

"Connection to DB is not open."

 

Exit

 

}

# Call the function that does the inserts.

 

Do-InsertValue ($sqlConnection)

 

# Close the connection.

 

if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {

 

$sqlConnection.Close()

 

 

}

 

}

}

But it is not inserting anything to table. Please help me out.

 

 

 

 

 

Thursday, May 12, 2016 - 11:32:37 AM - bass_player Back To Top (41471)

Hi Nandini,

You can simply use the output of the script in the first section and store it in a SQL Server database. You can either use the SQL Server PowerShell extensions to do this

https://sqlpsx.codeplex.com/

Or, in later versions of SQL Server, use the native PowerShell cmdlets like Invoke-SqlCmd

 

ForEach ($instance in Get-Content "D:\SQL_Servers.txt")
{
     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
     $dbs=$s.Databases      
     $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
}

 

Thursday, May 12, 2016 - 2:44:18 AM - Nandini De Back To Top (41463)

 Can you please tell me the script to post the output to remotely accessed SQL server Table?

 

Monday, July 13, 2015 - 5:06:52 PM - Ted Back To Top (38199)

Great stuff Edwin, I use PS in my daily work to do all kinds of tasks.  Thanks for sharing.

 

Ted Higgins

Wednesday, June 24, 2015 - 9:16:27 AM - bass_player Back To Top (38010)

You can use the ConvertTo-Html cmdlet to get you started. Of course, this does not create the fancy color-coded columns but it's enough to get you started

https://technet.microsoft.com/en-us/library/ee156817.aspx

Wednesday, June 24, 2015 - 5:33:17 AM - ATT Back To Top (38007)

Firs of all thank you for this script.

Being a beginner in Powershell, i do have my handsfull on workarounds. I tried to apply your script but unfortunately our environment does not have excelsheet installed and getting it installed is highly unlikely.

Maybe this is too much to ask but is there anyway this output can be saved in an html format rather than using excel?

Any help will be appreciated.

Friday, February 13, 2015 - 7:58:02 AM - Suresh Kumar Back To Top (36229)

Good post. I would like to add Database status description(online, offline etc) after space available (MB) column in the above script.. Can you please let me know where I can change the script.

 

Thanks in Advance.

 

Suresh Kumar.

Monday, December 15, 2014 - 2:40:13 PM - bass_player Back To Top (35612)

Hi Mahesh,

I noticed that you are on the Tables object inside the AdventureWorks2014 database. Is Created a property of the Table class?

$sheet.cells.Item($counter,3) = $_.created

I doubt it is based on this documentation. Use the CreateDate property instead

Monday, December 15, 2014 - 9:18:53 AM - Mahesh Back To Top (35609)

I am not able to insert Created date into excelsheet. datatype of Created date is datetime.

Please find the below code : 

cd sql\PC296353-PC\DEFAULT\databases\adventureworks2014\tables

$FilePath = "D:\test.xlsx"

$SheetName = "sheet2"

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$excel.displayalerts=$false

$workbook = $excel.Workbooks.open($FilePath)

$sheet = $workbook.sheets.item($sheetname)

$counter = 1

$sheet.cells.Item(1,1) = “Name”

$sheet.cells.Item(1,1).Font.Bold= $true

$sheet.cells.Item(1,2) = “Schema”

$sheet.cells.Item(1,2).Font.Bold = $true

$sheet.cells.Item(1,3) = “Created”

$sheet.cells.Item(1,3).Font.Bold = $true

 

ls | ForEach-Object {

    $counter++

    $sheet.cells.Item($counter,1) = $_.Name

    $sheet.cells.Item($counter,2) = $_.schema

    $sheet.cells.Item($counter,3) = $_.created

}

$workbook.saveas($filepath)

$workbook.close()

$excel.quit()

 

Get-Process excel | stop-process -Force

Monday, August 11, 2014 - 11:13:54 AM - bass_player Back To Top (34083)

There are a lot of ways to accomplish this task. One of them is mentioned here

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx

Monday, August 11, 2014 - 12:19:29 AM - Ajitesh Malhotra Back To Top (34074)

Nice job,

Hi,

ForEach ($instance in Get-Content "D:\SQL_Servers.txt")
{
       [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
       $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

       $dbs=$s.Databases
       $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
}

I Would like to insert the data in SQL Server table, Could you please help me out for the same.

Friday, December 6, 2013 - 10:37:05 AM - Margaret Back To Top (27718)

I took the loop out and just added the filegroup variable inside the foreach db loop and it is now working properly

 

Thanks!

Friday, December 6, 2013 - 9:38:43 AM - Margaret Back To Top (27716)

I need to include information from the filegroups.files for each database. I tried to include another foreach loop inside the foreach($db in $dbs), but it's not writing the information matched to the database associated with it, so I'm sure I have something wrong with that. Can you help me resolve this?

 #Get information about the database files
 ForEach  ($file in $db.FileGroups.Files)

  {
 $flMaxSize=$file.MaxSize
 $flGrowth = $file.Growth
 $flGT = $file.GrowthType

    $sheet.Cells.Item($intRow, 11) =  switch($flMaxSize){-1 {"Unlimited"} default {"{0:N2}" -f($value)}}
    $Sheet.Cells.Item($intRow, 12) = $flGrowth
    $Sheet.Cells.Item($intRow, 13) = $flGT

 $intRow++
 }

 

 

 

Friday, September 6, 2013 - 10:51:43 AM - bass_player Back To Top (26649)

Hi Mo,

I would assume that you have databases in your SQL Server instance that are marked as OFFLINE. Use the IsAccessible property to only retrieve databases that are online. For example, change the code $dbs = $s.Databases to something like $dbs = $s.Databases | Where {$_.IsAccessible -eq $true}

Friday, September 6, 2013 - 6:12:57 AM - Mo Back To Top (26642)

Im new to PS and have a newbie type question.

I have the script saved root of c, i have the server text file saved same place.

Windows server 2008 machine. 64 bit.

Im testing the code and trying to run it from Windows Powershell ISE.

so from the command window of PS_ISE - i do this : ./Spaceused_spread.ps1

Then i get a bunch of these

 
You cannot call a method on a null-valued expression.
At C:\temp\Powershell\SpaceUsed_Spread.ps1:95 char:28
+           $Sheet.Cells.Item <<<< ($intRow, 7) = $dbSpaceAvailable
    + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 
You cannot call a method on a null-valued expression.
At C:\temp\Powershell\SpaceUsed_Spread.ps1:96 char:28
+           $Sheet.Cells.item <<<< ($intRow, 7).Interior.ColorIndex = $fgColor
    + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Monday, July 1, 2013 - 3:21:47 AM - CLETO Back To Top (25641)

Thanks for a great article. I am new to PS

The script works fine for default port 1433, would like to know how to use the same script with a different port 1333

Some server, I get the message failed to connect.

 

Friday, April 26, 2013 - 12:13:29 PM - bass_player Back To Top (23577)

Hi Kalebb,

Instead of querying the MSDB database to retrieve the list of SQL Server Agent jobs, you can use the JobServer property of the Server object in SMO and retrieve the Jobs collection. From there, you can explore the different object properties to retrieve schedules, frequency, etc. Check out this article for more information.

http://www.mssqltips.com/sqlservertip/1798/checking-sql-server-agent-jobs-using-windows-powershell/

Friday, April 26, 2013 - 12:35:33 AM - Kalebb Back To Top (23568)

I tried to modify this script to retrieve a list of jubs running on each instance, but I cannot get to make this work. I'm new to Powershell so a liitle help would be really appreciated.

The Script I am using is:

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "C:\Instances.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "Job Name"
      $Sheet.Cells.Item($intRow,2) = "Job Enabled"
      $Sheet.Cells.Item($intRow,3) = "Frequency"
      $Sheet.Cells.Item($intRow,4) = "Start Date"
      $Sheet.Cells.Item($intRow,5) = "Start Time"
      $Sheet.Cells.Item($intRow,6) = "Max Duration"
      $Sheet.Cells.Item($intRow,7) = "Subday Frequency"

     #Format the column headers
     for ($col = 1; $col –le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }


     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell


     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

     #$dbs | "USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name',
 'Job Enabled' = CASE dbo.sysjobs.Enabled
  WHEN 1 THEN 'Yes'
  WHEN 0 THEN 'No'
 END,
 'Frequency' = CASE dbo.sysschedules.freq_type
  WHEN 1 THEN 'Once'
  WHEN 4 THEN 'Daily'
  WHEN 8 THEN 'Weekly'
  WHEN 16 THEN 'Monthly'
  WHEN 32 THEN 'Monthly relative'
  WHEN 64 THEN 'When SQLServer Agent starts'
 END,
 'Start Date' = CASE active_start_date
  WHEN 0 THEN null
  ELSE
  substring(convert(varchar(15),active_start_date),1,4) + '/' +
  substring(convert(varchar(15),active_start_date),5,2) + '/' +
  substring(convert(varchar(15),active_start_date),7,2)
 END,
 'Start Time' = CASE len(active_start_time)
  WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
  WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
  WHEN 3 THEN cast('00:0'
    + Left(right(active_start_time,3),1) 
    +':' + right(active_start_time,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(active_start_time,4),2) 
    +':' + right(active_start_time,2) as char (8))
  WHEN 5 THEN cast('0'
    + Left(right(active_start_time,5),1)
    +':' + Left(right(active_start_time,4),2) 
    +':' + right(active_start_time,2) as char (8))
  WHEN 6 THEN cast(Left(right(active_start_time,6),2)
    +':' + Left(right(active_start_time,4),2) 
    +':' + right(active_start_time,2) as char (8))
 END,
-- active_start_time as 'Start Time',
 CASE len(run_duration)
  WHEN 1 THEN cast('00:00:0'
    + cast(run_duration as char) as char (8))
  WHEN 2 THEN cast('00:00:'
    + cast(run_duration as char) as char (8))
  WHEN 3 THEN cast('00:0'
    + Left(right(run_duration,3),1) 
    +':' + right(run_duration,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 5 THEN cast('0'
    + Left(right(run_duration,5),1)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 6 THEN cast(Left(right(run_duration,6),2)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
 END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
  WHEN 0 THEN 'Once'
  ELSE cast('Every '
    + right(dbo.sysschedules.freq_subday_interval,2)
    + ' '
    +     CASE(dbo.sysschedules.freq_subday_type)
       WHEN 1 THEN 'Once'
       WHEN 4 THEN 'Minutes'
       WHEN 8 THEN 'Hours'
      END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
  FROM dbo.sysjobhistory
  GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',
 'Job Enabled' = CASE dbo.sysjobs.Enabled
  WHEN 1 THEN 'Yes'
  WHEN 0 THEN 'No'
 END,
 'Frequency' = CASE dbo.sysschedules.freq_type
  WHEN 1 THEN 'Once'
  WHEN 4 THEN 'Daily'
  WHEN 8 THEN 'Weekly'
  WHEN 16 THEN 'Monthly'
  WHEN 32 THEN 'Monthly relative'
  WHEN 64 THEN 'When SQLServer Agent starts'
 END,
 'Start Date' = CASE next_run_date
  WHEN 0 THEN null
  ELSE
  substring(convert(varchar(15),next_run_date),1,4) + '/' +
  substring(convert(varchar(15),next_run_date),5,2) + '/' +
  substring(convert(varchar(15),next_run_date),7,2)
 END,
 'Start Time' = CASE len(next_run_time)
  WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
  WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
  WHEN 3 THEN cast('00:0'
    + Left(right(next_run_time,3),1) 
    +':' + right(next_run_time,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(next_run_time,4),2) 
    +':' + right(next_run_time,2) as char (8))
  WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
    +':' + Left(right(next_run_time,4),2) 
    +':' + right(next_run_time,2) as char (8))
  WHEN 6 THEN cast(Left(right(next_run_time,6),2)
    +':' + Left(right(next_run_time,4),2) 
    +':' + right(next_run_time,2) as char (8))
 END,
-- next_run_time as 'Start Time',
 CASE len(run_duration)
  WHEN 1 THEN cast('00:00:0'
    + cast(run_duration as char) as char (8))
  WHEN 2 THEN cast('00:00:'
    + cast(run_duration as char) as char (8))
  WHEN 3 THEN cast('00:0'
    + Left(right(run_duration,3),1) 
    +':' + right(run_duration,2) as char (8))
  WHEN 4 THEN cast('00:'
    + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 5 THEN cast('0'
    + Left(right(run_duration,5),1)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
  WHEN 6 THEN cast(Left(right(run_duration,6),2)
    +':' + Left(right(run_duration,4),2) 
    +':' + right(run_duration,2) as char (8))
 END as 'Max Duration',
    CASE(dbo.sysschedules.freq_subday_interval)
  WHEN 0 THEN 'Once'
  ELSE cast('Every '
    + right(dbo.sysschedules.freq_subday_interval,2)
    + ' '
    +     CASE(dbo.sysschedules.freq_subday_type)
       WHEN 1 THEN 'Once'
       WHEN 4 THEN 'Minutes'
       WHEN 8 THEN 'Hours'
      END as char(16))
    END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
  FROM dbo.sysjobhistory
  GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]"

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

Regards.

 

Tuesday, May 8, 2012 - 3:27:30 AM - Prashant Back To Top (17321)

Excellent work! Very useful!!

Tuesday, March 27, 2012 - 6:07:50 PM - bass_player Back To Top (16653)

This is one of the reasons to really get into PowerShell since Microsoft is standardizing server management using PowerShell. I wrote a script a few years ago that does the same thing - go thru all of the servers in the data center and report disks that have free disk space less than the specified threshold and send an email alert - all of this in 3 lines of code.

Glad to know that the scripts are valuable

Tuesday, March 27, 2012 - 4:10:45 PM - Chris Back To Top (16651)

Exactly, that is what I am doing now. I actually have it setup to run on a particular server, fetch all the data into a particular database, and then I use a procedure with HTML to select from the table and send out a Daily Space Check email. It works great!

Tuesday, March 27, 2012 - 2:11:39 PM - Leon Back To Top (16646)

Great example. With a few tweaks, you can turn this code into health check of your sql farm.

Wednesday, March 21, 2012 - 11:25:19 AM - Chris Back To Top (16563)

Thank you very much! I was paying too much attention to the Powershell script and not my own SQL. Very much appreciated.

Tuesday, March 20, 2012 - 5:15:38 PM - Greg Robidoux Back To Top (16548)

Chris,

I haven't used PowerShell much,but try this change

exec ('use ['+@db + ']')

Tuesday, March 20, 2012 - 4:21:58 PM - Chris Back To Top (16547)

I am using the following query to try to check database sizes on each of our servers(fairly similar to what you are doing). It works for all databases except for those that have spaces in their names. I am new to Powershell and have not been able to come up with any way to fix it. Right now, for those that have spaces, it only trys to read the first segment of the name and therefore cannot find the database in the server.databases table. Any help would be appreciated!


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

sqlcmd -S starling\reno_dev -E -Q "truncate table master.dbo.FileStats"

$servers = get-content c:\InstanceList.txt
$query = "DECLARE @db varchar(max)
SET @db = DB_NAME()
exec ('use '+@db)
SELECT CONVERT(sysname, SERVERPROPERTY('ServerName')) AS ServerName
,DB_NAME() AS db
,[fileid],
[File_Size_MB] = convert(decimal(12,2),round([size]/128.000,2))
,[Space_Use_MB] = convert(decimal(12,2),round(fileproperty([name],'SpaceUsed')/128.000,2))
,[Free_Space_MB]=convert(decimal(12,2),round(([size]-fileproperty([name],'SpaceUsed'))/128.000,2))
,[Percent_Free_MB]=CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2))
,CASE WHEN maxsize =-1 THEN 'unlimited'
            WHEN maxsize/128 < 1024 THEN CAST(maxsize/128 as varchar)
            ELSE CAST(maxsize/(128*1024) as varchar)
            END as maxsize
,RTRIM(LTRIM([name])) AS FileName
,RTRIM(LTRIM([filename])) AS FilePath
FROM dbo.sysfiles"

foreach($server in $servers)
{
$sqlserver = new-object “Microsoft.SqlServer.Management.Smo.Server” $server
foreach ($db in $sqlserver.Databases)
{
$db_name=[string]$dbfix
invoke-sqlcmd -Query $query -database $dbfix -ServerInstance $sqlserver.name -IgnoreProviderContext
}
}


Saturday, January 8, 2011 - 7:03:54 AM - Jean-Michel Jarry Back To Top (12538)

Monday, December 20, 2010 - 3:54:46 PM - Susan Van Eyck Back To Top (10472)

Totally awesome tip!  I'm (slowly) working on putting together some PS scripts to keep an eye on my servers and databases.  This gives me a huge head start.  Thanks!

Tuesday, October 20, 2009 - 8:58:40 AM - bass_player Back To Top (4263)

If it is just the output without the Excel thing, you can simply do this. Remember, I am just reading the names of the SQL Server instances from a text file so you can simply use that variable beside the database name

#################################################
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{

#GetDBs.ps1
#This script gets SQL Server database information using PowerShell


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

foreach ($db in $dbs)
    {
        Write-Host $instance , $db.Name
    }

}

Saturday, July 25, 2009 - 2:43:32 PM - mzigadlo Back To Top (3777)

Great script.

 I was looking to format my output like this....

server1,db1

server1,db2

server1,db3

server2,db1

but haven't been able to figure out how to get $s.name and $s.databases to output that way.  Any suggestions?

Friday, July 3, 2009 - 7:56:04 AM - Sanjacinto Back To Top (3690)

Hi, I too got the same error. I've found the following code but can't seem to get any further :-(

$xl = New-Object -comobject "excel.application"

$xl.visible = $true

$xlbooks =$xl.workbooks

$newci = [System.Globalization.CultureInfo]"en-GB"

$xlbooks.PSBase.GetType().InvokeMember("Add", [Reflection.BindingFlags]::InvokeMethod, $null, $xlbooks, $null, $newci)

Any ideas how I can incorporate the code from the article below, into the above, so I can reference the $Sheets object? - ($Sheets.Cells.Item() etc)

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)

Thursday, May 28, 2009 - 9:42:01 AM - bass_player Back To Top (3465)

This might be because of the difference between the local user settings and Office settings.  Check the Regional Settings in your user profile or you can include a line in the PowerShell script to call the CultureInfo class but that's a bit geeky

CultureInfo Class

http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo.aspx 

Here's a Microsoft KB article for this

http://support.microsoft.com/default.aspx?scid=kb;en-us;320369


Thursday, May 28, 2009 - 9:30:40 AM - bass_player Back To Top (3464)

You can add this inside the ForEach loop for the databases collection but you do need to have it formatted properly in Excel as each database would have at least two files

    $fileGroups = $db.FileGroups
    ForEach ($fg in $fileGroups)
    {
        $fg.Files | Select $db.Name, Name, FileName, size, UsedSpace
    }
    $db.LogFiles | Select $db.Name, Name, FileName, Size, UsedSpace


Wednesday, May 27, 2009 - 4:05:22 PM - Jacare Back To Top (3459)

When I try to execute a following statement $Excel = $Excel.Workbooks.Add() in powershell I receive this error:

"Old format
or invalid type library. (Exception from HRESULT: 0x80028018
(TYPE_E_INVDATAREAD))" message..

What can I do to get around this ?

Wednesday, May 27, 2009 - 12:40:11 PM - sqljim Back To Top (3455)

Great tip. I was trying to add the datafiles, log files and their sizes to the export to Excel but, couldn't get the right property. Suggestion on how to get it?














get free sql tips
agree to terms