Microsoft SSMS July 2016 Hotfix is Available now

Go and get it.  Of course, No SQLServer PowerShell update on this hotfix. But you’ll need this update.

MSSMS_02_2016-07-15_14-32-33

Read more on this link for more information: https://msdn.microsoft.com/en-us/library/mt238290.aspx

If you are still interested of see what’s new in this July Edition of SSMS, check the following Microsoft Blog site:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

MSSMS_01_2016-07-15_14-32-33

 

Create PowerShell Top-Down ETL Process III

On previous blog we have collect some data and built our .NET PowerShell object which is going to be loaded into our SQL Server.

In this final section we are completing the process of loading the data into SQL Server. We need to make sure we got our Database and table previously created and ready to receive our data.

For this process we’ll need to create 3 functions:
1. ConnectTo-SQLServer
2. Load-LogRecord
3. Process-PSObjectToSQL

Of course we could do everything in one long script file.  But, by breaking out into it will make it much easier to handle and maintain.

In order to connect to SQL Server, we’ll be using straight SMO classes to load the data. Also, we’ll  integrate some basic error catching using “try-catch” code block structure.

I’m keeping the functions in a basic level hardcoding some the PSObject variables instead of using parameter names. Keep in mind, the use of parameter name(s) in a function brings a lot flexibility and function reusability.

Function – ConnectTo-SQLServer

Using SMO with a few lines of code we connect to a SQL Server engine. Here’s the basic script code to will allow us to initiate a connection to SQL Server:

function ConnectTo-SQLServer
{
 [CmdletBinding()]
 param ()
 ## - Select SQLServer:
 $global:SQLServerInstanceName = "MTRINIDADLT2";
 $global:SQLServerDatabaseName = "LogEntries";
 $global:logServerName = "tcgsvvm04";
 
 ## - Loading SMO .NET Assembly: (Required)
 [system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
 
 ## - Connecting to SQL Server (Windows Authentication) and building you table object:
 $global:SQLSvrObj = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstanceName;
 $global:SQLSvrObj.ConnectionContext.StatementTimeout = 0;
};

You will notice the use the of “$global:variablename” with some of the variable objects created. This way we won’t loose the value stored in my PSObject(s) like “$global:SQLSvrObj” and have it available to other PowerShell function(s).

Function – Load-logRecord

This function show a Kind-Of template for using T-SQL insert statement to an existing table and at the same time subtitute the PSObject variable(s) constructing full qualify database table name. We construct the T-SQL Insert string and then to execute the query using SMO database *method “.ExecuteNonQuery($global:insertqry)”.

function Load-logRecord
{
CmdletBinding()]
Param ()
$global:insertQry = @"
INSERT INTO [$($global:SQLServerDatabaseName)].[dbo].[$($global:logServerName)_Log]
([Column1]
, [Column2]
, [Column3]
, [Column4]
, [Column5]
, [Column6]
, [Column7]
, [Column8]
, [Column9]
, [Column10]
, [Column11]
, [Column12]
, [Column13]
, [Column14]
, [Column15]
, [ServerName])
VALUES
( '$($global:l.Column1)'
, '$($global:l.Column2)'
, '$($global:l.Column3)'
, '$($global:l.Column4)'
, '$($global:l.Column5)'
, '$($global:l.Column6)'
, '$($global:l.Column7)'
, '$($global:l.Column8)'
, '$($global:l.Column9)'
, '$($global:l.Column10)'
, '$($global:l.Column11)'
, '$($global:l.Column12)'
, '$($global:l.Column13)'
, '$($global:l.Column14)'
, '$($global:l.Column15)'
, '$($global:LogServerName)')
GO
"@;
 $global:SQLSvrObj.Databases["Master"].ExecuteNonQuery($global:insertQry);
};

*Note: Keep in mind, by saving the PSObject variable with a $global: scope, you will access to its value after the function has been executed or the value will be dispose (null).

Function – Process-PSObjectToSQL

This is the heart of loading the data.  Here we use both previous functions: 1. ConnectTo-SQLServer and
2. Load-LogRecord.

This a simple code block using the ForEach() block to read thru the PSObject variable to load the data into SQL Server.  All accomplished with a few code block.

Now, here’s where we’ll include our error catching code block in case we encounter any data load issue during this process.

function Process-PSObjectToSQL
{
 [CmdletBinding()]
 param (
  [array]$SQLDataObj
 )
 ## Start process
 Try
 {
  ConnectTo-SQLServer;
  $global:cnt = 0
  Write-Verbose "Start loading Data to SQL Server" -Verbose;
  foreach ($global:l in $SQLDataObj)
  {
   Load-logRecord;
   $global:cnt++;
  }
 }
 catch
 {
  $global:ShowErr = @"
Insert SQL failed:  Record #[ $($global:cnt) ]`n`r-----
Table: [$($global:SQLServerDatabaseName)].[dbo].[$($global:logServerName)_Log]`n`r-----
$($global:insertQry)`n`r-----`n`r$($error[0].Exception)
"@
  [system.reflection.assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null;
  [System.Windows.Forms.MessageBox]::Show("$global:ShowErr", "SQLInsertQry Exception") | Out-Null;
  
  $errfound = $true
 }
 finally
 {
  ## - Cleanup connection: 
  $global:SQLSvrObj.ConnectionContext.Disconnect();
  $global:SQLSvrObj = $null;
  if ($errfound -eq $true)
  {
   Write-Verbose "Failure loading Data to SQL Server!" -Verbose;
   $global:ShowErr | Out-File -FilePath c:\Temp\SQLDataLoadfailure.txt;
   Invoke-Item c:\Temp\SQLDataLoadfailure.txt;
  }else{
   Write-Verbose "Completed loading Data to SQL Server" -Verbose;
  }
 }
};

This function has a ParameterName $SQLDataObj.  This will take the previously created $SQLData PSObject to be loaded to SQL Server.

In Error catching code block, the try{..} contains all the logic code to process the data. Then, the catch{..} has a custom string with enough information to trouble the issue the process may have encountered, such as: Fully qualified Database name, Insert Query T-SQL script, and the actual Exception error message.

At the end of the process, error or not, the finally{..} block will always execute the code. In this case, to disconnect and cleanup the connection to SQL Server.

Executing the process

After all the previous functions has been loaded, just type the following one-liner:

Process-PSObjectToSQL -SQLDataObj $SQLData;

This sample script code can serve as a startup Template to load data into SQL Server.

This sample SQL data load will fail. Here’s when the Try/Catch/Finally will work for you in trapping what went wrong. Adding the necessary code to provide that additional information to troubleshoot and fix the problem.

Be Creative!  Check out the results.

  1. Data exception when inserting a record which was a transformation error in one of the fields.SampleSQLDataLoad_01
  2. Results generated from the data exception can be use to find what went wrong with this T-SQL insert script. SampleSQLDataLoad_02
  3. Error exception code could generate an output file. SampleSQLDataLoad_03
  4. Then, after fixing the issue, the data was completely loaded to SQL Server. SampleSQLDataLoad_04

Happy PowerShell!

Florida PowerShell Upcoming activities for May and June 2016

It’s a busy and a good time to learn some PowerShell.

1. Where: Florida PowerShell User Group Monthly meeting, Date: Thursday, May 26th at 6:30pm. Online
Topic: The Essential PowerShell on Error Trapping.
Description:
Do you to learn how to trap and document error while running PowerShell scripts? This session will cover the use and how to trap errors in your PowerShell script. We’ll be creating simple script providing some scenarios in trapping errors. At the same time, we are going to end up creating an error report.

Register at Eventbrite: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-may-2016-tickets-25454080841

2. Where: Idera Geek Synch, Date: Wednesday, June 1st at 12:00pm – 01:00pm, Online
Topic: The Essential PowerShell Tools for the DBA Administrator.
Description: I will covers the some of my favorite PowerShell tools I uses on a regular basis. In this session I will be showing some available tools the DBA can use along with PowerShell. I’ll be integrating Visual Studio with PowerShell and at the same time using IDERA’s PowerShellPlus editor. At the end, we’ll build an SSIS package solution to execute our PowerShell script. It will be pack with interesting thing to do as well as help you accomplish your automation tasks.

Register at: https://www.idera.com/events/geeksync

3. Where: IT Pro Camp Jacksonville. Date: Saturday, June 11th All Day Event. (In Person)
Topic: The Essentials of Tackling PowerShell Basic Functions
Description:  I will demonstrate creating a PowerShell function from a one-liner and/or an existing script file in its basic form. This is an example of the script evolution which you’ll experience while building you PowerShell skills.

Register at: http://itprocamp.com/

4. Where: SQLSaturday South Florida. Date: Saturday, June 18th, All Day Event. (In Person)
Topic: SSIS – Integrating PowerShell in a ScriptTask component
Description: This session will demostrate how you can reuse a PowerShell script in SSIS “Script Task” component as part on a ETL flow.  I’ll be showing some basic .NET Script code in both C# and VB.  I’ll be adding some useful tips when re-using existing Powershell code. Integrating different .NET technologies in a SSIS package: C#, VB.NET, XML, and PowerShell.

Register at: http://www.sqlsaturday.com/524/EventHome.aspx

Come and say Hi!

Azure PowerShell Preview 1.0 is here

Welcome to PowerShell Azure Resource Manager (RM)!

Microsoft has introduce and made available Azure PowerShell Preview 1.0.  Please read all about it in the following Azure Blog site:

Also, feel free to search for it at usign the following link:

Some Interesting links 

Well, within the search there are more interesting blog articles to start doing some PowerShell scripting:

How to install and configure Azure PowerShell: https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Using Azure PowerShell with Azure Resource Manager: https://azure.microsoft.com/en-us/documentation/articles/powershell-azure-resource-manager/

Manage Azure SQL Database with PowerShell: https://azure.microsoft.com/en-us/documentation/articles/sql-database-command-line-tools/

Integrating SQL AlwaysOn with Azure Site Recovery: https://azure.microsoft.com/en-gb/blog/integrating-sql-alwayson-with-azure-site-recovery/

Get it at PowerShell Gallery

Now, my question here is, did you found Azure PowerShell 1.0 Preview? Probably not, but in fact, it’s AzureRM 1.0.1 to be installed. Although, I did found Azure version 0.9.11 and, Yes! I did download that one just in case.

To download these new bits from the PowerShell Gallery, check out the “Get Started with the PowerShell Gallery” link: http://www.powershellgallery.com/pages/GettingStarted

AzureRM_01_10-16-2015

Search for both “Azure” and AzureRM”.

AzureRM_02_10-16-2015

AzureRM_03_10-16-2015

During my quest to download Azure PowerShell 1.0, I realized that its AzureRM the one I need to download using: “Install-Module AzureRM” followed by “Install-AzureRM -force“.

AzureRM_04_10-16-2015

To start using the AzureRM Cmdlets, just run the “Import-AzureRM” command.

AzureRM_07_10-16-2015

Now, we are ready to play with Azure Resource Manager and Azure SQL databases.   There’s a total of 653 *AzureRM* cmdlets.

AzureRM_08_10-16-2015

AzureRM_09_10-16-2015

Run the following command to list all *AzureRM* commands:

Import-AzureRM; Get-Command *AzureRM*;

 

In my next blog article I’ll be converting my classic Azure SQL script to use the Azure RM new paradigm.

 

PowerShell connecting to SQL Server without SMO

Sometimes there will be a need to provide a scripting solution to connect to SQL Server to execute either a TSQL query or Stored-Procedure without the need of installing a SQL Server bits.  Here I’m providing two code snippets that gets the job done without the use of SMO (SQL Server Management Object) class.

Executing a T-SQL Query

Use the System.Data.SqlClient namespace from the .NET Framework Data Provider for SQL Server in order to build functions to execute T-SQL statement(s) and/or SQL Stored-Procedure with PowerShell.

Here’s an example in how to execute a T-SQL statement querying a table in its basic form:

## - Set PowerShell variables:
$ConnectionString = 'server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!';
$TSQLQuery = "Select * from AdventureWorks2014.dbo.AWBuildVersion;";

## - Connect and Execute Stored-Procedure:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery, $ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;

 

nonSMO_01_10-16-2015

Executing a T-SQL Stored-Procedure

Now the code for executing SQL Stored-Procedure will be differect as we are going to use the ‘.sqlCommand‘ class to process the T-SQL Statement to run a custom table update procedure in its basic form:

## - Set PowerShell variables:
$ConnectionString = 'server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!';
$TSQLQuery = "Exec [AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion];";

## - Connect and Execute Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open()
$sqlCmd = New-Object Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlCon
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
$sqlCon.close();

I use dbForge for SQL Server to create new Stored-Procedure to dynamically increase the version number.

nonSMO_dbForge_01_10162015

After executing the non-SMO code then the record did change:

nonSMO_01a_10-16-2015

nonSMO_dbForge_02_10162015

Most important, when using the Data.SqlClient.SqlConnection class, the connection need be ‘.Close()‘ after executing the T-SQL command.

Error Handling

As we are connecting to the SQL Server to execute T-SQL Commands, its important to add some error handling routine to trap errors during the connectivity and/or T-SQL Statement(s) processing.  And, we do this by evolving the basic PowerShell code into a function.

Lets create two functions that will accept two parameters: a connection string, and the T-SQL Query.

* Get-TSQLQuery function

function Get-TSQLQuery
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)
Try
{
## - Non_SMO Get SQL query:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery,

$ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;
}
Catch
{
Write-Verbose "Error executing T-SQL Query: `r`n[$($error[0])]" -Verbose;
};
};

To test this function copy/paste the code into your PowerShell console, and the execute the following commands:

## - Set PowerShell variables:
$conn = 'server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

ID=sa;Password=$Adm1n!';
$tsql = "Select * from AdventureWorks2014.dbo.AWBuildVersion";

## - Execute the function with its parameters:
(Get-TSQLQuery -ConnectionString $conn -TSQLQuery $tsql) `
| Select-Object `
SystemInformationID, 'Database Version', VersionDate, ModifiedDate `
| Format-Table -AutoSize;

nonSMO_02_10-16-2015

* Execute-TSQLStoredProc function

function Execute-TSQLStoredProc
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)

Try
{
## - NON-SMO executing TSQL Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open();
$sqlCmd = New-Object Data.SqlClient.SqlCommand;
$sqlCmd.Connection = $sqlCon;
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
}
Catch
{
Write-Verbose "Error executing T-SQL Stored-Procedure: `r`n[$($error[0])]"

-Verbose;
}; $sqlCon.close();

};

## - Set PowerShell variables:
$Conn = 'server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

ID=sa;Password=$Adm1n!';
$tsql = "Exec x[AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion]";

## - Execute the function with its parameters:
Execute-TSQLStoredProc -ConnectionString $conn -TSQLQuery $tsql;

nonSMO_03_10-16-2015

Adding the Try/Catch error handling block helps is trapping and displaying the errors.

This method serves as another alternative to connect to a SQL Server without the need to install an instance on a system.

 

 

PowerShell in South SQL Saturday 379 was a Great Success

SQLSaturdaySoFlorida2015

Once again I’m thankful to the organizers to have me speak at this “Awesome” event.  I appreciate the all whom attend my session meking it a Great Success and they got more.  My “PowerShell with Visual Studio SQL Data Tools” session became also a “SMO Simplicity Recap” session. They got two session in one.

SQLSat379_02

 

Session highlights

1. Visual Studio Community 2013 is the environment to use for integrated development by including Microsoft and Third-Party tools like:
a. PowerShell Tools for Visual Studio
b. Python Tools for Visual Studio
c. SQL Data Tools – Business Intelligent Developement
d. PowerShell Studio 2015  – call from within Visual Studio
e. PrimalXML 2015 – Call from within Visual Studio
d. And many more can be added…

SQLSat379_03

2. Visual Studio integration with either Team Foundation and Github repositories.

3. A quick dive in XML objects.

4. PowerShell error trapping in integrated solution.

SQLSat379_06

5. PowerShell SMO embedding and executing T-SQL code.

SQLSat379_05

During my presentation I demo for the first time how Visual Studio can trap PowerShell errors from within a SSIS Script Task component. And, everyone dropped their mounth. Beside the fact that you can also run and trap script errors by executing by itself.

SQLSat379_04

In the SMO session, I show how useful the ScriptBlock can be when embedding and running T-SQL code while PowerShell reads one object at a time.

To download my presentation and demo files are all available under South Florida SQLSaturday Schedule page:
http://www.sqlsaturday.com/379/Sessions/Schedule.aspx

Once again, THANKS to everyone for your attendance and support.

June Month for learning PowerShell and SQL Server SMO

Yes! This is month you could learn PowerShell and SQL Server Management Object (SMO) techniques to start building script(s) to manage your SQL Server(s).

I will be speaking at the following events:

SQLSaturdaySoFlorida2015

1. South Florida SQL Saturday #379 on 06/13/2015 all day event. Session: “PowerShell with Visual Studio SQL Data Tools” (03:00PM – 04:15 PM) Register at:  http://www.sqlsaturday.com/379/Sessions/Schedule.aspx

GeekSynch

2. IDERA’s Geek Sync Webinar – “PowerShell Essentials using SQL Server SMO” on 06/16/2015 at 10:00 AM CT (11:00 AM EST). Register at: https://attendee.gotowebinar.com/register/5998631242300195841

At the SQLSaturday event there will some giveaways during my session.  Go ahead and register to any of these events.  It’s Free!

PowerShell SQLServer SMO Simplicity Series – 2

Now that we got the connection to the server there are a couple of lines we could include to avoid connection timeout during execution of our T-SQL script using SMO with PowerShell. So, in this blog will be covering executing a T-SQL script and viewing its results.

The *T-SQL script will build me table with data statistics about my selected Database indexes need to either Reorganize, or Rebuild.

*Note: The following T-SQL script modified from its original “Index optimization – REBUILD vs. REORGANIZE” by Author Sarjen Haque. It can be found at http://sqltouch.blogspot.com/2013/07/index-optimization-rebuild-and.html

$TSQLqry1 = @"
if object_id('$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]') is not null
begin
Drop Table $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList];
end

select
'$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]' as [IdxFragStatDatabase] ,
'$($DatabaseName)' as [IdxFragStatOnDatabase] ,
object_name(o.object_id) as [table_name] ,
schema_name(o.schema_id) as [schema_name] ,
i.name as [index_name] ,
i.type_desc as [index_type] ,
dmv.page_count as [Page_Count] ,
dmv.fragment_count as Fragment_Count,
round(dmv.avg_fragment_size_in_pages, 2, 2) as [avg_fragment_size_in_pages] ,
round(dmv.avg_fragmentation_in_percent, 2, 2) as [avg_fragmentation_in_percent] ,
case when dmv.avg_fragmentation_in_percent <= 5 then 'RELAX'
when dmv.avg_fragmentation_in_percent <= 30 then 'REORGANIZE' when dmv.avg_fragmentation_in_percent > 30 then 'REBUILD'
end as [action],
getdate() as [RunOnDate]
into $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]
from sys.partitions as p with ( readpast )
inner join sys.indexes as i with ( readpast ) on i.object_id = p.object_id
and i.index_id = p.index_id
inner join sys.objects as o with ( readpast ) on o.object_id = i.object_id
inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, N'LIMITED') dmv
on dmv.OBJECT_ID = i.object_id and dmv.index_id = i.index_id
and dmv.partition_number = p.partition_number
where objectproperty(p.object_id, 'ISMSShipped') = 0
and (i.name is not Null and i.type_desc <> 'HEAP')
order by [avg_fragmentation_in_percent] DESC,
[table_name],
[index_name]
"@;

Notice that PowerShell will substitute the values for $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList] within the Here-String @” .. “@. It’s important to notice when using Here-String TABS are not allow at the end of ‘@”;’ or you’ll get an error.

Back to the connection section. We need to include the following just after the ‘$MySQL = New-Object $MySQL = new-object Microsoft.SqlServer.Management.Smo.Server …‘:

$MySQL.ConnectionContext.ConnectTimeout = 21600;   (This is an example (optional) set to 21600 sec = 6hrs)
$MySQL.ConnectionContext.StatementTimeout = 0;   (This is an example set to 0 for no timeout when running T-SQL queries)

Here’s the sample for the code placement:

## - Connect and Execute T-SQL script:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerInstanceName;
#$MySQL.ConnectionContext.ConnectTimeout = 21600; #Optional#
$MySQL.ConnectionContext.StatementTimeout = 0;

If you don’t use the ‘ConnectionContext.StatementTimeout’ you’ll get a timeout error after 10 minutes of execution.

Now, we proceed to create our variables use for the T-SQL script:

[string] $SQLServerInstanceName = '.'
[string] $DatabaseName = 'AdventureWorks2014'
[string] $tblSchema = 'dbo'
[string] $IdxStatDatabase = 'devMaxTest'

The purpose of the $IdxStatDatabase is to redirect the data index stat report to another Database. Then, we use the following line to execute our T-SQL script using SMO with PowerShell.

$r = ($MySQL.Databases[$DatabaseName]).ExecuteWithResults($TSQLqry1);

After the PowerShell script execute, open SQL Server Management Studio (SSMS), go to the database where the index stat data is stored, and query the table to view results.

SQLPOSHSIMPLY2_01

This report will help you identify those indexes that need to be taken care off.

Full sample script below:

## - Set variables for T-SQL script:
$SQLServerInstanceName = '.';
$DatabaseName = "AdventureWorks2014";
$tblSchema = "dbo";
$IdxStatDatabase = "devMaxTest";

## - Build the T-SQL script for execution:
$TSQLqry1 = @"
if object_id('$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]') is not null
begin
Drop Table $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList];
end

select
'$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]' as [IdxFragStatDatabase] ,
'$($DatabaseName)' as [IdxFragStatOnDatabase] ,
object_name(o.object_id) as [table_name] ,
schema_name(o.schema_id) as [schema_name] ,
i.name as [index_name] ,
i.type_desc as [index_type] ,
dmv.page_count as [Page_Count] ,
dmv.fragment_count as Fragment_Count,
round(dmv.avg_fragment_size_in_pages, 2, 2) as [avg_fragment_size_in_pages] ,
round(dmv.avg_fragmentation_in_percent, 2, 2) as [avg_fragmentation_in_percent] ,
case when dmv.avg_fragmentation_in_percent <= 5 then 'RELAX'
when dmv.avg_fragmentation_in_percent <= 30 then 'REORGANIZE' when dmv.avg_fragmentation_in_percent > 30 then 'REBUILD'
end as [action],
getdate() as [RunOnDate]
into $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]
from sys.partitions as p with ( readpast )
inner join sys.indexes as i with ( readpast ) on i.object_id = p.object_id
and i.index_id = p.index_id
inner join sys.objects as o with ( readpast ) on o.object_id = i.object_id
inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, N'LIMITED') dmv
on dmv.OBJECT_ID = i.object_id and dmv.index_id = i.index_id
and dmv.partition_number = p.partition_number
where objectproperty(p.object_id, 'ISMSShipped') = 0
and (i.name is not Null and i.type_desc <> 'HEAP')
order by [avg_fragmentation_in_percent] DESC,
[table_name],
[index_name]
"@;

## - Connect, set timeout values:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerInstanceName;
#$MySQL.ConnectionContext.ConnectTimeout = 21600; #Optional#
$MySQL.ConnectionContext.StatementTimeout = 0;

## - Execute T-SQL script:
$r = ($MySQL.Databases[$DatabaseName]).ExecuteWithResults($TSQLqry1);
Write-Host "End of Script";

PowerShell using SMO to get SQL Database Table information

Using SQL Server Management Object .NET class can assist in extract information about your SQL Server engine.  This is a sample PowerShell script code using SMO to extract database tables information and display it in the PowerShell console;

## - SMO query for Tables information within the database:
## --

## - Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## - Setting variables with Server and Database name:
$SQLInstanceName = 'WIN81ENT01'; $Global:SourcedbName = 'AdventureWorks2014';

## - Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLInstanceName

## - Uncomment code block within #region - #endregion to enable use of SQL Authentication:
#region - Changing from default Windows to SQL Authentication:

#   ## - Variables required to SQL UserID and SQLPassword:
# $SQLUserName = "YourSQLUserID"; $sqlPwd = "YourSQLUSerPassword";
#
#   ## - Block of Code required to build SQL Authentication:
# $MySQL.ConnectionContext.LoginSecure = $false;
# $MySQL.ConnectionContext.set_Login($SQLUserName);
# $SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
# $MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);

#endregion

## - Create PSObject with all database table information:
$dbtables = $MySQL.Databases[$Global:SourcedbName].tables;

## - End of Script

Notice that in the code for connecting to SQL Server using SQL Authentication is included.  So, to enabled it, just uncomment the block of code, then provide the SQL UserName and Password.

After you have created the psObject with the database table information, we can proceed to display it on console using the Out-Gridview cmdlet.


## 1 - Display Table Information on a GridView:
$dbtables | Select-Object `
@{ Label = 'SQLServerName'; Expression = { ($SQLInstanceName) }; }, `
@{ Label = 'DatabaseName'; Expression = { ($_.Parent) }; }, `
@{ Label = 'TableName'; Expression = { ($_.Name) }; },
@{ Label = 'DataSpaceUsed(KB)'; Expression = { ($_.dataspaceused) }; }, `
@{ Label = 'IndexSpaceUsed(KB)'; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{
Label = "LastTableUpdate"; Expression = { `
$sqlupdtbl = @"
Select
--object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID('$($Global:SourcedbName)')
and Object_ID = Object_id('$($_.Name)')
"@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update;
}} | Out-GridView `
-Title "Display Database: $SourcedbName Tables Information";

Output to a gridview

Also, it’s possible to export the psobject information to a *.csv file format for later viewing using the Export-csv cmdlet.


## 2 - Create csv file:
$dbtables | Select-Object `
@{ Label = 'SQLServerName'; Expression = { ($SQLInstanceName) }; }, `
@{ Label = 'DatabaseName'; Expression = { ($_.Parent) }; }, `
@{ Label = 'TableName'; Expression = { ($_.Name) }; },
@{ Label = 'DataSpaceUsed(KB)'; Expression = { ($_.dataspaceused) }; }, `
@{ Label = 'IndexSpaceUsed(KB)'; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{ Label = "LastTableUpdate"; Expression = { `
$sqlupdtbl = @"
Select
--object_name(object_Id) as TableName,
last_user_update as [Last_Table_Update]
from sys.dm_db_index_usage_stats
where database_id = db_ID('$($Global:SourcedbName)')
and Object_ID = Object_id('$($_.Name)')
"@; `
$x = $MySQL.Databases[$Global:SourcedbName].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update; }} `
| Export-Csv -NoClobber -NoTypeInformation `
-Path "C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv";

## 3 - Open file:
Invoke-Item "C:\Temp\$SQLInstanceName_ListDatabase_$SourcedbName_TableInfo.csv";

Export to *.CSV file

Keep in mind, in order to use load and use the “Microsoft.SQLServer.Smo” assembly, you must had already install at least the SQL Server Management Studio or only using it’s individual components (http://www.maxtblog.com/2012/09/create-powershell-smo-scripts-without-installing-sql-server/).

For more information about SQL Server SMO check my article at:
http://sqlmag.com/powershell/using-sql-server-management-objects-powershell