Powershell Script for regular DBA activities_GenerateScripts

Some of the common routine activities that we do on Sql Server are :

1. Backup

2. Restore

3. Generate table scripts

For the point mentioned 1 and 2 above, I had tried to ease with this powershell utility  http://www.sqlservercentral.com/scripts/Backup/151723/

In this post, I will talk about a utility which would ease generating table scripts even by an app developer without accessing SSMS.

The advantage of this powershell utility is:

1. It allows the user to exclude identity columns in the data scripts

2. It allows the user to specify the number of rows to be populated in the data scripts.

Let me do a walkthrough !!

On executing this script, it asks for :

1. Server name
2. Database name
3. Mode of authentication : Windows/SQL
4. If mode of authentication is SQL, then it would ask Sql User name and Password
5. Table and Schema name for scripting
6. Various options for scripting

• Generate schema
• Generate data
• Check for object existence
• Script indexes
• Script foreign keys
• Whether to exclude identity columns
• Whether to limit the number of rows for data script

as seen in figure 1.0.

Fig1.0

we get the scripts generated as shown in figure 1.1

Fig1.1

If the user enables the feature to exclude identity columns by passing “y” to the option “Do you want to ignore identity columns while scripting data” as shown in figure 2.0 we get the output with the identity column “ResellerKey”  as shown in figure 2.1

Fig2.0

Fig2.1

The data script contains around 701 rows and we do not put a limit on number of rows as shown in figure 3.0 and 3.1

Fig3.0

Fig3.1

However, we may require a subset of rows for our testing purpose. Let’s say we need just 2 rows, then we need to pass below parameters:

Do you want to create data scripts for fixed number of rows  : y

Enter the number of rows required in the data script: 2

as shown in figure 4.0

Fig4.0

We see that we got just 2 rows in the data script as showing in figure 4.1

Fig4.1

How to use the script

Follow the below steps for executing the script:

1. Paste the powershell code mentioned in this article in a text editor and save the file with extension “ps1”, let’s say the file is saved as GenerateScripts.ps1

2. Let’s say the file is saved to a location : c:\utility\GenerateScripts.ps1, create a batch file with the name GenerateScripts.bat in the same location as the powershell file and paste below mentioned code in the batch file

@echo off
powershell -executionpolicy bypass -File .\GenerateScripts.ps1
pause

3. In order to execute the powershell, execute the batch file GenerateScripts.bat

3. After executing this utility, the scripts are saved in the same folder within the file “scripts.sql” file.

Note:

To execute the option for exclude identity columns and limit the number of rows in data scripts, the user should be a member of db_ddlAdmin or db_Owner role.

I would love to hear the feedback and suggestions on further improvements to this script !!

###############READ ME######################
#This script is meant to get the scripts for
#sql tables without connecting to SSMS.
#This would also help you to get the data script
#without identity columns.
#You can also specify the number of rows and 
#it will product data script for those number
#of rows
############################################

##############LOADING ASSEMBLIES####################
$assemblylist =   
"Microsoft.SqlServer.Management.Common",  
"Microsoft.SqlServer.Smo",  
"Microsoft.SqlServer.Dmf ", 
"Microsoft.SqlServer.SqlWmiManagement ",  
"Microsoft.SqlServer.ConnectionInfo ",  
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.Sdk.Sfc "

foreach ($asm in $assemblylist)  
{  
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)  
}  
#####################################################

$ServerName = ""
$DatabaseName = ""
$UserName = ""
$Password = ""
$AuthenticationType = "" 

Write-Host "Enter the server name :" -NoNewline -BackgroundColor black -ForegroundColor green
$ServerName = Read-Host

Write-Host "Enter the database name :" -NoNewline -BackgroundColor black -ForegroundColor green
$DatabaseName = Read-Host

Write-Host "Enter the Authentication mode(sql/windows) :" -NoNewline -BackgroundColor black -ForegroundColor green
$AuthenticationType = Read-Host

if($AuthenticationType -eq "sql"){
    Write-Host "Enter the user name :" -NoNewline -BackgroundColor black -ForegroundColor green
    $UserName = Read-Host

    Write-Host "Enter the password :" -NoNewline -BackgroundColor black -ForegroundColor green
    $Password = Read-Host
}
if($AuthenticationType -ne "windows" -and $AuthenticationType -ne "sql" ){
    Write-Host "Invalid authentication type, Program will exit now!!!" -BackgroundColor black -ForegroundColor Red
    exit
}



IF($AuthenticationType -eq "SQL"){
    $SqlConnectionString = "Server = $ServerName; Database = $DatabaseName; User ID= $UserName; Password= $Password"  #ConnectionString With Sql Authentication
}

IF($AuthenticationType -eq "Windows"){
    $SqlConnectionString = "Server = $ServerName; Database = $DatabaseName;Integrated Security=True"                 #ConnectionString with Windows Authentication
}
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server

try{
$srv.Refresh()
$srv.ConnectionContext.ConnectionString = $SqlConnectionString
$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter ($srv)
$database = $srv.databases.Item($DatabaseName)
}
catch{
write-host $_.Exception.Message -foregroundcolor "red"
write-host "Unable to connect to the server" -foregroundcolor "red"
exit
}



$opt_createSchema = "n"
$opt_createData = "n"
$opt_checkForObjectExistence = "n"
$opt_includeIndexes = "n"
$opt_includeForeignKey = "n"
$opt_createScriptWithoutIdentityColumns = "n"
$opt_createDataScriptsForFixedRows = "n"
$opt_rowsToInclude = 0

##########################################



##################FUNCTION ScriptTableManually#######################
function ScriptTableManually([string]$tableName,[string]$schemaName,[int] $rowsToInclude,[boolean] $excludeIdentity){
    #Write-Host "In ScriptTableManually"
    #Write-Host $tableName
    
    $filePath = $PSScriptRoot + '\script.sql'
    #Write-Host $filePath
    #if (Test-Path $filePath) {
    #  Remove-Item $filePath
    #}

    $colList = ""

    $colList = getColumnList $tableName $schemaName $excludeIdentity

    $SqlConnection.ConnectionString =  $SqlConnectionString    
    $SqlCommand.CommandTimeout = 0
    $SqlConnection.Open()
    if($rowsToInclude -eq 0){
    $sqlQuery = "select ",$colList.TrimStart(',')," into $schemaName.Temp_$tableName from $schemaName.$tableName"
    }
    else{
    $sqlQuery = "select TOP ",$rowsToInclude,$colList.TrimStart(',')," into $schemaName.Temp_$tableName from $schemaName.$tableName"
    }
    #write-host $sqlQuery
    $SqlCommand.Connection = $SqlConnection
    $SqlCommand.CommandText = $sqlQuery
    $SqlCommand.ExecuteNonQuery() | Out-Null;    
    $SqlConnection.Close()
    $database.Tables.Refresh()
    Write-Host "The file is getting prepared" -BackgroundColor white -ForegroundColor DarkCyan
    Foreach ($tblToScript in $database.tables | Where-Object{$_.name -eq "Temp_$tableName" -and $_.schema -eq $schemaName})
    {
        #$tblToScript.Name
        #Write-Host "In Foreach"
        Foreach($script in $scripter.EnumScript($tblToScript)){
           $script.replace("Temp_$tableName",$tableName) | Out-File $filePath -Append
        }
    }
    $SqlConnection.Open()
    $SqlCommand.CommandText = "Drop table $schemaName.Temp_$tableName"
    $SqlCommand.ExecuteNonQuery() | Out-Null;
    $SqlConnection.Close()
    Write-Host "The file is created @ $filePath" -BackgroundColor Green -ForegroundColor black
}



##################FUNCTION createTableWithIdent#######################
function createTableWithIdent([string]$tableName,[string]$schemaName){
    #Write-Host "In create table with ident"
    $filePath = $PSScriptRoot + '\script.sql'
    #Write-Host $filePath
    #if (Test-Path $filePath) {
    #  Remove-Item $filePath
    #}
    Write-Host "The file is getting prepared" -BackgroundColor white -ForegroundColor DarkCyan
    
    $tbl = $database.tables | Where-Object{$_.name -eq $tableName -and $_.schema -eq $schemaName}
    Foreach($tblName in $tbl){
        #Write-Host $tblName
        $scripter.EnumScript($tblName) | Out-File $filePath -Append
    }
    Write-Host "The file is created @ $filePath" -BackgroundColor Green -ForegroundColor black
}
##################function getColumnList#############################
function getColumnList([string]$tableName,[string]$schemaName,[boolean] $excludeIdentity){
    #Write-Host "In getColumnList Function"
    $colList = ""
    $tbl = $database.tables | Where-Object{$_.name -eq $tableName -and $_.schema -eq $schemaName}
    Foreach($tblCol in $tbl){
        if($excludeIdentity -eq $true){
            Foreach($colName in $tblCol.Columns | Where-Object{$_.identity -ne $true}){
                $colList += ",", $colName.Name
            }
        }
        else{
            Foreach($colName in $tblCol.Columns){
                $colList += ",", $colName.Name
            }
        }
    }
    #Write-Host $colList
    return $colList
}

#$tblName_toscript = Read-Host "Enter the table name to script"

Write-Host "Enter the table name to script: " -NoNewline -BackgroundColor black -ForegroundColor green
$tblName_toscript = Read-Host

Write-Host "Enter the schema name for this table: " -NoNewline -BackgroundColor black -ForegroundColor green
$schemaName_toscript = Read-Host

if($tblName_toscript.Trim().Length -eq 0 -or $schemaName_toscript.Trim().Length -eq 0){
    Write-Host "Invalid schema or table name, Program will exit now!!!" -BackgroundColor black -ForegroundColor Red
    exit
}

#Write-Host $tblName_toscript

$tbl = $database.tables | Where-Object{$_.name -eq $tblName_toscript -and $_.schema -eq $schemaName_toscript}
if($tbl.count -eq 0){ 
    Write-Host "No object found with given schema and tablename, Program will exit now!!!" -BackgroundColor black -ForegroundColor Red
    exit
}
Write-Host "####OPTIONS:Enter Y/y if you wish to say Yes else enter N/n####" -BackgroundColor white -ForegroundColor black
Write-Host "Do you want to generate schema script ? :" -NoNewline -BackgroundColor black -ForegroundColor green
$opt_createSchema = Read-Host 

Write-Host "Do you want to generate data script ? :" -NoNewline -BackgroundColor black -ForegroundColor green
$opt_createData = Read-Host 

if($opt_createSchema -ne "y" -and $opt_createData -ne "y"){
    Write-Host "The application will exit now !!!" -BackgroundColor black -ForegroundColor Red
    exit
}

if($opt_createSchema -eq "Y"){
    Write-Host "Do you want to check for object existence while generating schema script ? :" -NoNewline -BackgroundColor black -ForegroundColor green
    $opt_checkForObjectExistence = Read-Host 

    Write-Host "Do you want to script indexes ? :" -NoNewline -BackgroundColor black -ForegroundColor green
    $opt_includeIndexes = Read-Host
     
    Write-Host "Do you want to script foreign key ? :" -NoNewline -BackgroundColor black -ForegroundColor green
    $opt_includeForeignKey = Read-Host
}
if($opt_createData -eq "Y"){
    Write-Host "Do you want to ignore identity columns while scripting data ? :" -NoNewline -BackgroundColor black -ForegroundColor green
    $opt_createScriptWithoutIdentityColumns = Read-Host 

    Write-Host "Do you want to create data scripts for fixed number of rows ? :" -NoNewline -BackgroundColor black -ForegroundColor green
    $opt_createDataScriptsForFixedRows = Read-Host 

    if($opt_createDataScriptsForFixedRows -eq "Y"){ 
            Write-Host "Enter the number of rows required in the data script :" -NoNewline -BackgroundColor black -ForegroundColor green
            $opt_rowsToInclude = Read-Host
        }
}

$filePath = $PSScriptRoot + '\script.sql'
#Write-Host $filePath
if (Test-Path $filePath) {
    Remove-Item $filePath
}
if($opt_checkForObjectExistence -eq "Y"){$scripter.Options.IncludeIfNotExists = $true} else{$scripter.Options.IncludeIfNotExists = $false}
if($opt_includeIndexes -eq "Y"){$scripter.Options.Indexes = $true} else{$scripter.Options.Indexes = $false}
if($opt_includeForeignKey -eq "Y"){$scripter.Options.DriForeignKeys = $true} else{$scripter.Options.DriForeignKeys = $false}
if($opt_createSchema -eq "Y"){$scripter.Options.ScriptSchema = $true} else{$scripter.Options.ScriptSchema = $false}
if($opt_createSchema -eq "Y"){
    $scripter.Options.ScriptData = $false
        Foreach ($tblToScript in $database.tables | Where-Object{$_.name -eq "$tblName_toscript" -and $_.schema -eq $schemaName_toscript})
        {
            Foreach($script in $scripter.EnumScript($tblToScript)){
              $script | Out-File $filePath -Append
            }
        }
    }

if($opt_createData -eq "Y"){
        $scripter.Options.ScriptData = $true
        $scripter.Options.ScriptSchema = $false
    } 
    else{
        $scripter.Options.ScriptData = $false
        $scripter.Options.ScriptSchema = $false
    }

if($opt_createData -eq "Y"){
    if($opt_createScriptWithoutIdentityColumns -eq "Y"){
        if($opt_createDataScriptsForFixedRows -eq "Y"){
            ScriptTableManually $tblName_toscript $schemaName_toscript $opt_rowsToInclude $true
        } 
        else{
           ScriptTableManually $tblName_toscript $schemaName_toscript 0 $true
        }
    }
    else{ 
        if($opt_createDataScriptsForFixedRows -eq "Y"){
            ScriptTableManually $tblName_toscript $schemaName_toscript $opt_rowsToInclude $false
        } 
        else{
           createTableWithIdent $tblName_toscript $schemaName_toscript
        }
    }
}