<#
.Synopsis
Umożliwia zrzut skryptu T-SQL indeksów z bazy danych
.DESCRIPTION
Umożliwia zrzut skryptu T-SQL indeksów z bazy danych
.EXAMPLE
Zrzut skryptu dla dwoch indeksow
Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID')
.EXAMPLE
Zrzut skryptu dla dwoch indeksow i zapisanie do pliku
.\Get-CreateIndexScript.ps1 -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID') -Verbose | Out-File C:\file.sql
.EXAMPLE
Zrzut skryptów dla indeksów pobranych z pliku płaskiego
Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName (Get-Content C:\Temp\indexes.txt) -Verbose | Out-File D:\Temp\
.LINK
Author: Mateusz Nadobnik
Link: blog.mnadobnik.pl
Date: 22.10.2016
Version: 0.0.1.1
Keywords:
Notes:
ChangeLog: 22.10.2016 - napisanie skryptu
30.10.2016 - dodanie obługi błędów
#>
[CmdletBinding()]
param(
#Address to SQL Server
[Parameter(Mandatory=$true)]
[string]$SqlServer,
#Database Name
[Parameter(Mandatory=$true)]
[string]$DatabaseName,
#Array with index names
[Parameter(Mandatory=$true)]
[array]$IndexName
)
Begin
{
try
{
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
}
catch
{
Write-Warning $Error[0]
return
}
$connectsqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
$connectsqlserver.ConnectionContext.ApplicationName = "Powershell - Get-CreateIndexScript"
$connectsqlserver.ConnectionContext.ConnectTimeout = 1
Write-Verbose "Connect to server $SqlServer"
if ($connectsqlserver.ConnectionContext.IsOpen -eq $false)
{
$connectsqlserver.ConnectionContext.Connect()
}
$a = "'" + ($IndexName -join "','") + "'"
$query = "Select i.name as index_name, t.name as table_name, SCHEMA_NAME(schema_id) as schema_name
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s
Join sys.indexes i
On i.object_id = s.object_id and i.index_id = s.index_id
Inner join sys.tables t on i.object_id = t.object_id and t.object_id = s.object_id
Where i.name is not null and i.name in ($a)"
Write-Verbose "Query `n $query"
try
{
$db = $connectsqlserver.Databases.Item($DatabaseName)
[array]$Indexes = ($db.ExecuteWithResults($query)).Tables[0]
}
catch
{
Write-Warning $Error[0]
return
}
[string]$Script = "/****** Dump of index - $SqlServer Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`nUSE [$DatabaseName]`nGO`n`n"
}
Process
{
foreach ($row in $Indexes)
{
try
{
Write-Verbose "Get script for $($row.index_name)"
$Script += "/****** Object: $($row.index_name) Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`n"
$Script += $connectsqlserver.databases[$DatabaseName].Tables.Item($row.table_name,$row.schema_name).Indexes[$row.index_name].Script()
$Script += ";`nGO `n`n"
}
catch
{
Write-Warning $Error[0]
}
}
}
End {
$connectsqlserver.ConnectionContext.Disconnect()
return $Script
}
