Functions to assist with Windows PowerShell Profiles

This script hold a total of 4 functions to help setup your missing PowerShell User profiles which are not created when installing Window Management Framework, nor setup during then new Windows 7/2008R2 Operating Systems.

Get this script at the Microsoft Technet Script Gallery: http://gallery.technet.microsoft.com/scriptcenter/717b8ca4-391d-4fef-87fc-153c133606c7

You’ll have the following functions available to setup all your profiles:

  1. Set-PSUserProfile – Will create a blank Windows PowerShell User profile.
  2. Set-PSISEProfile – Will create a blank Windows PowerShell ISE User profile.
  3. Get-PSUserProfile – Will display or edit the Windows PowerShell User profile.
  4. Get-PSISEProfile – Will display or edit the Windows PowerShell ISE User Profile.

Also, all four commands use the parameter “-OpenWithEditor” in case you want to
make changes to any of the profile files.  All functions have aliases.

To load all functions use the following command:

PS Z:\YourScriptLocation>. .\ Beginner_PSProfile_Helper_functions.ps1

Use the help command to get information about any of the 4 functions:

PS> Help Get-PSUserProfile -Detailed

See folowing images:

Brief sample of the profile helper functions
Sample of the function using the "-OpenWithEditor" parameter

Recognition
To Michael Simmon which his blog gave the idea to create these functions: http://www.ilovepowershell.com/how-to-create-a-powershell-profile/

I hope you’ll find it useful.

Three Twitter PowerShell Anouncements…

First, at today PowerShell Deep Dive sessions, Jeffrey Snover (Microsoft Father of PowerShell) has tweeted:

“ANNOUNCING: #PowerShell Licensed under the Community Promise http://tinyurl.com/3d5vcf3 WHOOOOT!!”

Then, SAPIEN Technologies tweeted: (Check their blog!!)

“New: : It’s All New! http://www.sapien.com/blog/2011/04/18/its-all-new/

Finally, PowerShell MVP – Jaykul, has release in codeplex – Show-UI is the merger of PowerBoots and WPK.

“The new url is official http://showui.codeplex.com/ #PowerBoots #PowerShell #WPK #WPF #GUI #Threading”

There’s lot to learn and no time to waste.  Happy Scripting!!

 

Getting Database Tables record and Data Space Used count

I created the function “Get-dbTableRecordCount” to get both the tables record and data space used from a selected table.  A the same time, if you can’t remember the databasename, then you can use the parameter ” -ShowAllDatabases Y ” to only list all databases from the provided SQL Server Instance name.

Download “Get-dbTableRecordCount” Function script at the MS Script Gallery at: http://gallery.technet.microsoft.com/scriptcenter/c606e406-af61-4552-b686-2c9dc17ab06e

A few requirements will remain:

1. Must have either SQL Server 2008 (or above) installed, or the have the free version of SQL Server Management Studio 2008 R2 (no SQL Engine) installed.

2. And, to download either,  or both of the SQLPS modules.  Instructions are provided with the description of the function in the MS Script Gallery.

This functions will have the following 3 parameters:

1. ” -SQLInstanceName “ – This is mandatory entry. Enter your SQL Server instance name.

2. ” -DatabaseName “ – Optional but required with for the “-SQLInstanceName” parameter.

3. ” -ShowAllDatabses “ – Optional but only requiered with the “-SQLInstanceName” parameter and not with “-DatabaseName”.

An Alias name is provided: “gtrc

Also, remember you can use “help”, or “get-help” commands to read documentation about this function.

Sample pics: (show databases only)

gtrc -SQLInstanceName XXXXX -ShowAllDatabases Y
gtrc -SQLInstance XXXXX -DatabaseName YYYYYY

Application: You can save the results adn generate different out results.

Happy scripting!!

Check for SQL Server(s) Version with Get-MSSQLVersion function

I just posted a new PowerShell function to help get information about your SQL Server(s).  Following Don Jones recent post about “Inventory SQL Server Versions on Multiple Computers” this is just to show you, if you have SQL Server 2008 (R2 or above), then you can use SMO which is already installed with SQL.

Check out *”Get-MSSQLVersion” at Script Gallery: http://gallery.technet.microsoft.com/scriptcenter/5f763a6e-23bf-46be-a837-13e5005acb72

Please, make sure to meet the requirements to use this function.  More information in the Script Gallery post.

Now, this new function include three cmdlet parameters that will affect your results.

  • ” -SQLServerOption ” – This parameter will allow you to: 1) Individually get version information about one, or a series of SQL Server, or 2) using the ” -SQLServerOption all ” parameter, will get you a list of all SQL Servers on the network.
  • -DateTimeStamped ” – *This parameter only accepts Y, any other character(s) will be ignored. This will results in writting the start and end date/time for running this command.
  • -HideBlank ” – This parameter only accepts Y, any other character(s) will be ignored.  This will ignored all blanks items selected with the ” -SQLServerOption ALL” because   you may not have permission to access the SQL Instance.

By the way, I automatically included some SQL Server counter to let you know how many server(s) it found and the how many are listed in the results.

*Note: I included the Date/Time stamped is because this function will take some time to complete. 

Sample picture:

Practical Use: – The following line will create a text file with your information:   

PS> gmsv -SQLServerOption ALL | Out-File c:\MyFolder\MySQLVersionInfo.txt

Also, you can save the results to an object, then create a csv file.  Here’s the starting point for your CSV:

Go ahead, and complete the task…  I’m Just Saying!!

 Have fun Scripting!!

Get-dbTableColumns function submitted to the Script Gallery

Based on recent article about getting SQL Server table columns information, I decided to create a function “Get-dbTableColumns.ps1” and posted into the Microsoft Scritp Gallery. I use some of our favorite community tools from Chad Miller and Pinal Dave to bring in a practical and useful command.

So, checked it out at, “Get database table columns list”: http://gallery.technet.microsoft.com/scriptcenter/21c90399-d408-428d-92a5-902e037fdd9b

Instructions are included.

Hint: Want to save result to a text file? Try…

Get-dbTableColumns -DatabaseName YourDbName | Out-File -FilePath c:\TableInfo.txt

Enjoy!

SQL PowerShell – Enchancing Listing your DB Table Information

I love the PowerShell collaboration in the IT community.  One of my twitter followers reminded me about the “Format-Table” command  that allows you to group your collection using the parameter “-GroupBy” which work beautifully.  There’s no need to add the “ForEach” command.  I trully forgot to the check for this parameter. 

This change in my script will saves be  a few more lines of code.and here’s the final result.  Here’s the script:

## - Stored T-SQL modified script into a string variable
$sqlQuery = @"
SELECT
s.name+'.'+OBJECT_NAME(c.OBJECT_ID) as SchemaTableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.tables as t2 on t2.object_id = c.object_id
JOIN sys.schemas as s on s.schema_id = t2.schema_id
ORDER BY c.OBJECT_ID;
"@

## - Load the SQLPS module for Denali ( for 2008/200R2 is SQLPS)
Import-Module SQLPSv2
$SavedResults1 = Invoke-SQLCmd -ServerInstance "ISO-DESKTOP-65" -Database "ReportServer" -Query $sqlQuery
$SavedResults1 | ft -auto -GroupBy SchemaTableName

And, here’s the sample PowerShell Console result:

This is a good example of how PowerShell can keep simplifying your script as yiou continue to grow.

Especial THANKS to David Moravec (http://www.powershell.cz) for tweet to giving me the tip to enhance this script.   Now, you can download both versions:

<>

SQL PowerShell – List your DB Table columns information…

Making some addition to Pinal Dave T-SQL code provided in one of his blog on “List All The Column With Specific Data Types” we can use PowerShell to list our database table’s columns in an efficient way.  But, for the information what I needed to produce I had to add a few things to Pinal’s code.

Keep in mind, PowerShell can assist the DBA in effectively providing better T-SQL results.  Let me show you how we can leverage this T-SQL code with PowerShell.

Here’s the original SQL script executed in SSMS and the actual results shown in the following picture:

(Pinal Dave SQL Script)


This is a start but I needed a little more information, and a better way to display it.

First, I added two other ‘joins’ to the SQL script to include the ‘sys.tables’ and ‘sys.schemas’. Then, I customized the script a little more to get the results I wanted and ran it on my SSMS:

SELECT s.name+‘.’+OBJECT_NAME(c.OBJECT_ID) as SchemaTableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.tables as t2 on t2.object_id = c.object_id   JOIN sys.schemas as s on s.schema_id = t2.schema_id
ORDER BY c.OBJECT_ID;


As you can see, now I included Schema with Table names and removed some columns. Now, let’s put PowerShell in action. I’m going to use my modified T-SQL script, create a simple PowerShell script and run it under SQL PowerShell.

Prerequisite for executing this script, you need to have:

  1. SQL Server 2008 (or above)
    1. Download Chad Miller SQLPS module for SQL Server 2008 and 2008 R2
    2. Or, Download my version of SQLPSv2 for SQL Server ‘Denali’.
  2. PowerShell 2.0

Using the any of the SQLPS modules, you can create a simple PowerShell script to generate our table columns information with a few lines of code:

  1. Store the T-SQL script code in a string variable.
  2. Load the SQL PowerShell Module using the “Import-Module” command.
  3. Run the T-SQL query using the “Invoke-SQLCmd” to store the information in a variable.
  4. Then, we can manipulate the results we just saved in a variable.

Here’s the script:

$sqlQuery = @
SELECT
s.name+’.’+OBJECT_NAME(c.OBJECT_ID) as SchemaTableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,c.max_length
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.tables as t2 on t2.object_id = c.object_id
JOIN sys.schemas as s on s.schema_id = t2.schema_id
ORDER BY c.OBJECT_ID;
@
Import-Module SQLPSv2
$SavedResults1 = Invoke-SQLCmdServerInstance
“MAX-PCWIN1”Database
“Adventureworks”Query
$sqlQuery
$SavedResults1 | ftauto

The last line of this script will list all content of your stored results and display it in a table format.


We got all tables listed one after another. So, we can add a few more lines of code to separate the tables:

## – First create a list of all the tables in the stored object ‘$SavedResults1’
$TableName = $SavedResults1 | selectunique SchemaTableName | Sort SchemaTableName
## – Verify all tables selected and in order
$TableName

Next step is to use a “ForEach-Object” command to help us break out the tables information individually, and display it on the PowerShell console:

foreach($t
in
$TableName){
    $SavedResults1 | where {$_.SchemaTableName -eq
$t.SchemaTableName} | `
    select SchemaTableName,ColumnName,SchemaName,TypeName,max_length | FTAuto
}


Now, you can be creative in how this information can be displayed, or even exported to another file format such as a ‘CSV’ or ‘TXT’ type. Just try it!!

Here’s the complete simple PowerShell script: 

 

 Stay tuned for more!  

To get script file, download here: