Tag: SQLPSX

Generate T-SQL Script for a List of Views

USE: 

CLEAR 

$sSRCServer = "TUIRA"
$sSRCDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $true
$oSO.IncludeIfNotExists = $true

$a = Get-Content "C:tempviewlist.txt"

foreach ($o in $a) {
	Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlView -Name $o| `
                     Get-SqlScripter -scriptingOptions $oSO
	WRITE("GO `n`n`n")
}

 

Script Out All Indexes and Primary Keys from a Database

USE: To generate T-SQL code for all indexes and PK constraints from a database.

CLEAR 

$sSRCServer = "TPW520"
$sSRCDatabase = "iSQLPS" 

$oSO = New-SqlScriptingOptions
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.NonClusteredIndexes = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Indexes
    
	foreach ($oIndex in $oT) {
		$DDL = Get-SqlScripter $oIndex -scriptingOptions $oSO
	    
		"$DDL `nGO `n`n"
	}
}