Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Part 1 - Based on a Blog http://www.iteggs.com/2016/10/27/117/
#----------------------------------------------------------------------#
#
# Powershell Script Name : Debug-sql.ps1
# Created : 27.10.2016
# Created by : Gisli Gudmundsson
# LinkedIN : https://is.linkedin.com/in/gisli-gudmundsson-11a77639
# License Usage :
# This code can be used for private use only
# You may modify this code and distribute
#
#----------------------------------------------------------------------#
Import-Module C:\DynamicDocumentation\Dependencies\DEPMSSQLActions.psm1 -Force -Verbose
$Environment = "production"
Write-Host "`n`n"
Write-Host "Running SQL Debug Script" -ForegroundColor Yellow
Write-Host "................................"
Write-Host "1. Trying to add row to ADUsersCount table" -ForegroundColor Cyan
AddMSSQLUserCount -UserCount 50 -UserType "ServiceUsers" -CostCenter "DEBUG" -Environment $Environment
Write-Host "2. Showing debug values in ADUsersCount table" -ForegroundColor Cyan
$TableInfo = DebugShowMSSQLTable -TableName "ADUsersCount" -ColumnName "UserCostCenter" -Environment $Environment
if($TableInfo -eq $null){
Write-Host "Error : Failure to retrieve table data" -ForegroundColor Red
}else{
$TableInfo
}
Write-Host "3. Cleaning up debug in ADUsersCount table" -ForegroundColor Cyan
DebugCleanUpMSSQLUserCount -TableName "ADUsersCount" -ColumnName "UserCostCenter" -Environment $Environment
#----------------------------------------------------------------------#
#
# Module Name : DEPMSSQLActions.psm1
# Created : 27.10.2016
# Created by : Gisli Gudmundsson
# LinkedIN : https://is.linkedin.com/in/gisli-gudmundsson-11a77639
# License Usage :
# This code can be used for private use only
# You may modify this code and distribute
#
#----------------------------------------------------------------------#
#Import the SQL Server Powershell Module
Import-Module SqlServer
#Import custom made modules
Import-Module C:\DynamicDocumentation\Settings\GlobalVariables.psm1 -Force
#Usage "RunQuery -Query $Query"
function RunQuery($Query, $Environment){
$ServerInstance = ServerInstance
$DatabaseName = DatabaseEnvironment -Environment $Environment
#Runs specific query that is added to the parameter
Invoke-Sqlcmd -Query $Query -ServerInstance $ServerInstance -Database $DatabaseName
}
#Usage "AddMSSQLUserCount -UserCount $Integer -UserType $StringMax50Chars -CostCenter $StringMax50Chars
function AddMSSQLUserCount($UserCount, $UserType, $CostCenter, $Environment){
#Adds new row into the database
$Insert_Query = "
INSERT INTO ADUsersCount (UserCount, UserType, UserCostCenter) VALUES ('$UserCount','$UserType','$CostCenter')
"
#Calls the Run-Query function
RunQuery -Query $Insert_Query -Environment $Environment
}
#Usage "DebugShowMSSQLTable -TableName"
function DebugShowMSSQLTable($TableName, $ColumnName, $Environment){
$Show_Query = "
SELECT * FROM $TableName WHERE $ColumnName = 'DEBUG'
"
RunQuery -Query $Show_Query -Environment $Environment
}
#Usage "DebugCleanUpMSSQLUserCount -TableName $TableName -ColumnName $ColumnName -Environment $Environment"
function DebugCleanUpMSSQLUserCount($TableName, $ColumnName, $Environment){
#Removes all data where debug row inserts has been added
$Clean_Query = "
DELETE FROM $TableName
WHERE $ColumnName = 'DEBUG'
"
RunQuery -Query $Clean_Query -Environment $Environment
}
#----------------------------------------------------------------------#
#
# Module Name : GlobalVariables.psm1
# Created : 27.10.2016
# Created by : Gisli Gudmundsson
# LinkedIn : https://is.linkedin.com/in/gisli-gudmundsson-11a77639
# License Usage :
# This code can be used for private use only
# You may modify this code and distribute
#
#----------------------------------------------------------------------#
$ServerInstance = "TSTSQL01"
$ProdDatabaseName = "DynamicDocumentationDB"
$DevelopmentDatabaseName = "DevDynamicDocumentationDB"
$TestDatabaseName = "DevDynamicDocumentationDB"
#Returns the value of ServerInstance
function ServerInstance(){ return $ServerInstance }
#Return the value of database environment, can be production, development and test
function DatabaseEnvironment($Environment){
$Environment = $Environment.ToLower()
if($Environment -eq "production"){ return $ProdDatabaseName }
if($Environment -eq "development"){ return $DevelopmentDatabaseName }
if($Environment -eq "test"){ return $TestDatabaseName }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.