Last active
October 13, 2018 16:41
-
-
Save wsmelton/b28eeece65ca1fbd56404b2fddeabd30 to your computer and use it in GitHub Desktop.
VS Code snippets for dbatools functions
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Show hidden characters
| { | |
| // Snippets to be used for creating new functions with dbatools module. | |
| // These snippets follow our general standard on the base parameters and properties we require in a function. | |
| // This requires VS Code 1.28 (September build) or higher, that supports snippets scoped to the workspace/project level | |
| // https://code.visualstudio.com/updates/v1_28#_project-level-snippets | |
| // To create the file: CTRL + SHIFT, P then type in Preferences: Configure User Snippets | |
| // Select the option for the project "dbatools" | |
| // Paste the contents of this gist and save the file. | |
| "Get function for SMO object": { | |
| "scope": "powershell", | |
| "prefix": "getsmo", | |
| "body": [ | |
| "function Get-Dba$0 {", | |
| "<#", | |
| "\t.SYNOPSIS", | |
| "\t\t${1:Short of what command does}", | |
| "", | |
| "\t.DESCRIPTION", | |
| "\t\t${2:Longer of what command does}", | |
| "", | |
| "\t.PARAMETER SqlInstance", | |
| "\t\tSQL Server name or SMO object representing the SQL Server to connect to. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.", | |
| "", | |
| "\t.PARAMETER SqlCredential", | |
| "\t\tLogin to the target instance using alternate Windows or SQL Login Authentication. Accepts credential objects (Get-Credential).", | |
| "", | |
| "\t.PARAMETER EnableException", | |
| "\t\tBy default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.", | |
| "\t\tThis avoids overwhelming you with a \"sea of red\" exceptions, but is inconvenient because it basically disables advanced scripting.", | |
| "\t\tUsing this switch turns this \"nice by default\" feature off and enables you to catch exceptions with your own try/catch.", | |
| "", | |
| "\t.NOTES", | |
| "\t\tTags: ${3:Singular word for command index}", | |
| "\t\tAuthor: ${4:FirstName} ${5:LastName} (@${6:TwitterHandle}), ${7:blog address}", | |
| "", | |
| "\t\tWebsite: https://dbatools.io", | |
| "\t\tCopyright: (c) 2018 by dbatools, licensed under MIT", | |
| "\t\tLicense: MIT https://opensource.org/licenses/MIT", | |
| "", | |
| "\t.LINK", | |
| "\t\thttps://dbatools.io/Get-Dba$0", | |
| "", | |
| "\t.EXAMPLE", | |
| "\t\tPS C:\\> Get-Dba$0 -SqlInstance localhost", | |
| "", | |
| "\t\t${8:Add minimum example for each parameter}", | |
| "", | |
| "#>", | |
| "\t[CmdletBinding()]", | |
| "\tparam (", | |
| "\t\t[parameter(Position = 0, Mandatory, ValueFromPipeline)]", | |
| "\t\t[Alias(\"ServerInstance\", \"SqlServer\")]", | |
| "\t\t[DbaInstance[]]\\$SqlInstance,", | |
| "\t\t[Alias(\"Credential\")]", | |
| "\t\t[PSCredential]\\$SqlCredential,", | |
| "\t\t[object[]]\\$${9:YourParameter},", | |
| "\t\t[switch]\\$EnableException", | |
| "\t)", | |
| "\tprocess {", | |
| "\t\tforeach (\\$instance in \\$SqlInstance) {", | |
| "\t\t\tWrite-Message -Level Verbose -Message \"Attempting to connect to \\$instance\"", | |
| "\t\t\ttry {", | |
| "\t\t\t\t\\$server = Connect-SqlInstance -SqlInstance \\$instance -SqlCredential \\$SqlCredential", | |
| "\t\t\t}", | |
| "\t\t\tcatch {", | |
| "\t\t\t\tStop-Function -Message \"Failure\" -Category ConnectionError -ErrorRecord \\$_ -Target \\$instance -Continue", | |
| "\t\t\t}", | |
| "", | |
| "\t\t\t# Add in filter code", | |
| "", | |
| "\t\t\t# Add in processing SMO object", | |
| "", | |
| "\t\t\t<# Output must include 3 properties for each object:", | |
| "\t\t\t\t1. ComputerName (\\$server.ComputerName)", | |
| "\t\t\t\t2. InstanceName (\\$server.ServiceName)", | |
| "\t\t\t\t3. SqlInstance (\\$server.DomainInstanceName)", | |
| "", | |
| "\t\t\t\t\"Select_DefaultView -InputObject \\$object -Property <property list\" can be used to only output certain properties", | |
| "\t\t\t\t\"Select_DefaultView -InputObject \\$object -ExcludeProperty <property list\" can be used to output everything but the list of properties", | |
| "\t\t\t#>", | |
| "\t\t}", | |
| "\t}", | |
| "}" | |
| ], | |
| "description": "template for building a function to get properties from SMO object" | |
| }, | |
| "Set function": { | |
| "scope": "powershell", | |
| "prefix": "setsmo", | |
| "body": [ | |
| "function Set-Dba$0 {", | |
| "<#", | |
| "\t.SYNOPSIS", | |
| "\t\t${1:Short of what command does}", | |
| "", | |
| "\t.DESCRIPTION", | |
| "\t\t${2:Longer of what command does}", | |
| "", | |
| "\t.PARAMETER SqlInstance", | |
| "\t\tSQL Server name or SMO object representing the SQL Server to connect to. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.", | |
| "", | |
| "\t.PARAMETER SqlCredential", | |
| "\t\tLogin to the target instance using alternate Windows or SQL Login Authentication. Accepts credential objects (Get-Credential).", | |
| "", | |
| "\t.PARAMETER EnableException", | |
| "\t\tBy default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.", | |
| "\t\tThis avoids overwhelming you with a \"sea of red\" exceptions, but is inconvenient because it basically disables advanced scripting.", | |
| "\t\tUsing this switch turns this \"nice by default\" feature off and enables you to catch exceptions with your own try/catch.", | |
| "", | |
| "\t.NOTES", | |
| "\t\tTags: ${3:Singular word for command index}", | |
| "\t\tAuthor: ${4:FirstName} ${5:LastName} (@${6:TwitterHandle}), ${7:blog address}", | |
| "", | |
| "\t\tWebsite: https://dbatools.io", | |
| "\t\tCopyright: (c) 2018 by dbatools, licensed under MIT", | |
| "\t\tLicense: MIT https://opensource.org/licenses/MIT", | |
| "", | |
| "\t.LINK", | |
| "\t\thttps://dbatools.io/Set-Dba$0", | |
| "", | |
| "\t.EXAMPLE", | |
| "\t\tPS C:\\> Set-Dba$0 -SqlInstance localhost", | |
| "", | |
| "\t\t${8:Add minimum example for each parameter}", | |
| "", | |
| "#>", | |
| "\t[CmdletBinding(SupportsShouldProcess, ConfirmImpact = \"High\")]", | |
| "\tparam (", | |
| "\t\t[parameter(Position = 0, Mandatory, ValueFromPipeline)]", | |
| "\t\t[Alias(\"ServerInstance\", \"SqlServer\")]", | |
| "\t\t[DbaInstance[]]\\$SqlInstance,", | |
| "\t\t[Alias(\"Credential\")]", | |
| "\t\t[PSCredential]\\$SqlCredential,", | |
| "\t\t[object[]]\\$${9:YourParameters},", | |
| "\t\t[switch]\\$EnableException", | |
| "\t)", | |
| "\tprocess {", | |
| "\t\tforeach (\\$instance in \\$SqlInstance) {", | |
| "\t\t\tWrite-Message -Level Verbose -Message \"Attempting to connect to \\$instance\"", | |
| "\t\t\ttry {", | |
| "\t\t\t\t\\$server = Connect-SqlInstance -SqlInstance \\$instance -SqlCredential \\$SqlCredential", | |
| "\t\t\t}", | |
| "\t\t\tcatch {", | |
| "\t\t\t\tStop-Function -Message \"Failure\" -Category ConnectionError -ErrorRecord \\$_ -Target \\$instance -Continue", | |
| "\t\t\t}", | |
| "", | |
| "\t\t\t# Add code for parameter filters", | |
| "", | |
| "\t\t\tforeach (\\$object in \\$objects) {", | |
| "\t\t\t\tif (\\$Pscmdlet.ShouldProcess(\"\\$object on \\$server\", \"What it will do\")) {", | |
| "\t\t\t\t\t\t# Work to be done", | |
| "", | |
| "\t\t\t\t\t\t[PSCustomObject]@{", | |
| "\t\t\t\t\t\t\tComputerName = \\$server.NetName", | |
| "\t\t\t\t\t\t\tInstanceName = \\$server.ServiceName", | |
| "\t\t\t\t\t\t\tSqlInstance = \\$server.DomainInstanceName", | |
| "\t\t\t\t\t\t\tObject = \\$object.Name", | |
| "\t\t\t\t\t\t\tStatus = \"Dropped/Updated/etc\"", | |
| "\t\t\t\t\t\t}", | |
| "\t\t\t\t\t}", | |
| "\t\t\t}", | |
| "\t\t}", | |
| "\t}", | |
| "}" | |
| ], | |
| "description": "template for building a function to set properties from SMO object" | |
| }, | |
| "Set function": { | |
| "scope": "powershell", | |
| "prefix": "setsql", | |
| "body": [ | |
| "function Set-Dba$0 {", | |
| "<#", | |
| "\t.SYNOPSIS", | |
| "\t\t${1:Short of what command does}", | |
| "", | |
| "\t.DESCRIPTION", | |
| "\t\t${2:Longer of what command does}", | |
| "", | |
| "\t.PARAMETER SqlInstance", | |
| "\t\tSQL Server name or SMO object representing the SQL Server to connect to. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.", | |
| "", | |
| "\t.PARAMETER SqlCredential", | |
| "\t\tLogin to the target instance using alternate Windows or SQL Login Authentication. Accepts credential objects (Get-Credential).", | |
| "", | |
| "\t.PARAMETER EnableException", | |
| "\t\tBy default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.", | |
| "\t\tThis avoids overwhelming you with a \"sea of red\" exceptions, but is inconvenient because it basically disables advanced scripting.", | |
| "\t\tUsing this switch turns this \"nice by default\" feature off and enables you to catch exceptions with your own try/catch.", | |
| "", | |
| "\t.NOTES", | |
| "\t\tTags: ${3:Singular word for command index}", | |
| "\t\tAuthor: ${4:FirstName} ${5:LastName} (@${6:TwitterHandle}), ${7:blog address}", | |
| "", | |
| "\t\tWebsite: https://dbatools.io", | |
| "\t\tCopyright: (c) 2018 by dbatools, licensed under MIT", | |
| "\t\tLicense: MIT https://opensource.org/licenses/MIT", | |
| "", | |
| "\t.LINK", | |
| "\t\thttps://dbatools.io/Set-Dba$0", | |
| "", | |
| "\t.EXAMPLE", | |
| "\t\tPS C:\\> Set-Dba$0 -SqlInstance localhost", | |
| "", | |
| "\t\t${8:Add minimum example for each parameter}", | |
| "", | |
| "#>", | |
| "\t[CmdletBinding(SupportsShouldProcess, ConfirmImpact = \"High\")]", | |
| "\tparam (", | |
| "\t\t[parameter(Position = 0, Mandatory, ValueFromPipeline)]", | |
| "\t\t[Alias(\"ServerInstance\", \"SqlServer\")]", | |
| "\t\t[DbaInstance[]]\\$SqlInstance,", | |
| "\t\t[Alias(\"Credential\")]", | |
| "\t\t[PSCredential]\\$SqlCredential,", | |
| "\t\t[object[]]\\$${9:YourParameters},", | |
| "\t\t[switch]\\$EnableException", | |
| "\t)", | |
| "\tbegin {", | |
| "\t\t\\$sql = \"--FORMART YOUR T-SQL, poorsql.com", | |
| "\t\t\t\\-- build your query based on parameters passed in", | |
| "\t\t\"", | |
| "\t}", | |
| "\tprocess {", | |
| "\t\tforeach (\\$instance in \\$SqlInstance) {", | |
| "\t\t\tWrite-Message -Level Verbose -Message \"Attempting to connect to \\$instance\"", | |
| "\t\t\ttry {", | |
| "\t\t\t\t\\$server = Connect-SqlInstance -SqlInstance \\$instance -SqlCredential \\$SqlCredential", | |
| "\t\t\t}", | |
| "\t\t\tcatch {", | |
| "\t\t\t\tStop-Function -Message \"Failure\" -Category ConnectionError -ErrorRecord \\$_ -Target \\$instance -Continue", | |
| "\t\t\t}", | |
| "", | |
| "\t\t\t# Add code for parameter filters", | |
| "", | |
| "\t\t\tforeach (\\$object in \\$objects) {", | |
| "\t\t\t\ttry {", | |
| "\t\t\t\t\t\\$results = \\$server.Query(\\$sql)", | |
| "\t\t\t\t}", | |
| "\t\t\t\tcatch {", | |
| "\t\t\t\t\tStop-Function -Message \"Something went wrong\" -Continue -Target \\$object -ErrorRecord \\$_", | |
| "\t\t\t\t}", | |
| "\t\t\t\tforeach (\\$row in \\$results) {", | |
| "\t\t\t\t\t# Work to be done", | |
| "", | |
| "\t\t\t\t\t[PSCustomObject]@{", | |
| "\t\t\t\t\t\tComputerName = \\$server.NetName", | |
| "\t\t\t\t\t\tInstanceName = \\$server.ServiceName", | |
| "\t\t\t\t\t\tSqlInstance = \\$server.DomainInstanceName", | |
| "\t\t\t\t\t\tObject = \\$row.ColumnName", | |
| "\t\t\t\t\t\tStatus = \"Dropped/Updated/Whatever\"", | |
| "\t\t\t\t\t}", | |
| "\t\t\t\t}", | |
| "\t\t\t}", | |
| "\t\t}", | |
| "\t}", | |
| "}" | |
| ], | |
| "description": "template for building a function to set properties from SMO object" | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment