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;
[sourcecode language=”powershell”]
## – 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
[/sourcecode]
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.
[sourcecode language=”powershell”]
## 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”;
[/sourcecode]
Also, it’s possible to export the psobject information to a *.csv file format for later viewing using the Export-csv cmdlet.
[sourcecode language=”powershell”]
## 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”;
[/sourcecode]
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