{"id":14900,"date":"2020-10-18T16:46:58","date_gmt":"2020-10-18T14:46:58","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/"},"modified":"2025-10-01T11:47:48","modified_gmt":"2025-10-01T09:47:48","slug":"sql-server-generating-sql-script-using-powershell-and-template-file","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/","title":{"rendered":"SQL Server: Generating SQL script using PowerShell and Template file"},"content":{"rendered":"<p>In this blog post, I will share with you a small PowerShell script I did recently.<\/p>\n<p>I have noticed that my customer performs a very repetitive and time-consuming task almost every day.<br \/>\nNew columns are added to tables on their business-critical database and they need to maintain SQL scripts file with all the ALTER TABLE statements for each new column.<\/p>\n<p>For every new column, my customer copy-pastes the following SQL Script and then change parts of it.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [4,5,6,9,20,21,22,25,26,28,33]\">\/***********************************\n*\n* New column \n*            Schema:       Order\n*            Table:        TestTable2     \n*            Column:       ColumnName1    \n*            \n* History    \n*            Date:         18\/10\/2020 \n*            User:         Steven Naudet \n*\n************************************\/\n\nIF NOT EXISTS (\n       SELECT * \n       FROM sys.tables AS t \n       JOIN sys.[columns] AS c ON t.[object_id] = c.[object_id]\n       JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id]  \n       WHERE 1=1 \n       AND s.name = 'Order'  \n       AND t.name = 'TestTable2' \n       AND c.name = 'ColumnName1' \n) \nBEGIN \n       PRINT 'Altering table Order.TestTable2 adding column [ColumnName1]' ; \n       ALTER TABLE [Order].TestTable2 \n       ADD \n       ColumnName1 NOT NULL; \nEND \n\n\/***********************************\n*\n* End New column ColumnName1  \n*\n************************************\/<\/pre>\n<p>The highlighted lines are manually edited by my customer every time there&#8217;s a new column to be added to the database, which can occur 20 times per week.<br \/>\nI decided to write a PowerShell function to do this task faster so my customer can work on more interesting things instead.<\/p>\n<p>The idea is to use a Template file for the SQL Script. The file is similar to the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/template\/templates-ssms\">SSMS templates<\/a>.<br \/>\nThe PowerShell script modifies the template and as output sends the SQL to Clipboard using <a href=\"https:\/\/docs.microsoft.com\/en-us\/powershell\/module\/microsoft.powershell.management\/set-clipboard\">Set-Clipboard<\/a>.<br \/>\nConsecutive calls to the function will add the SQL commands after one another in the Clipboard. This way my customer can just Paste the generated SQL script to his SQL source control tool.<\/p>\n<p>You can see the script in action with the GIF below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter \" src=\"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif\" alt=\"PowerShell Script in action GIF\" width=\"787\" height=\"420\"><\/p>\n<p>Here is the script.<\/p>\n<pre class=\"brush: powershell; gutter: true; first-line: 1\">function New-AddColumnSQL {\n\n    [CmdletBinding()]\n    param (\n        [Parameter(Mandatory=$true)][string] $Schema,\n        [Parameter(Mandatory=$true)][string] $Table,\n        [Parameter(Mandatory=$true)][string] $Column,\n        [Parameter(Mandatory=$true)][string] $Type,\n        [Parameter(Mandatory=$false)][string] $defaultValue,\n        [Parameter(Mandatory=$false)][switch] $isNotNull = $false,\n        [Parameter(Mandatory=$false)][string] $User = 'Steven NAUDET'\n    )\n\n    $TemplateFile = 'Z:\\scripts\\TemplateAddColumn.sql'\n\n    $Clipboard = Get-Clipboard\n    \n    # Clear Clipboard if first call to the function\n    if ($Clipboard -like '*Altering table*') {\n        $returnMessage = 'SQL Script appended to Clipboard'\n    } else {\n        $returnMessage = 'SQL Script pasted to Clipboard'\n        Set-Clipboard -Value $null\n    }\n\n    $ColumnDef = $Type\n\n    # NOT NULL\n    if($isNotNull) { \n        $ColumnDef = $ColumnDef + ' NOT'\n    }\n    $ColumnDef = $ColumnDef + ' NULL'\n\n    # DEFAULT value\n    if($defaultValue) { \n        $ColumnDef = $ColumnDef + ' DEFAULT ' + $defaultValue\n    }\n\n    $SQLscript = Get-Item -Path $TemplateFile | Get-Content\n    \n    $SQLscript = $SQLscript.Replace('&lt;Date&gt;', (Get-Date -UFormat \"%d\/%m\/%Y\"))\n    $SQLscript = $SQLscript.Replace('&lt;SchemaName&gt;', $Schema)\n    $SQLscript = $SQLscript.Replace('&lt;TableName&gt;', $Table)\n    $SQLscript = $SQLscript.Replace('&lt;ColumnName&gt;', $Column)\n    $SQLscript = $SQLscript.Replace('&lt;UserName&gt;', $User)\n    $SQLscript = $SQLscript.Replace('&lt;ColumnDefinition&gt;', $ColumnDef)\n\n    Set-Clipboard $SQLscript -Append\n\n    return $returnMessage\n\n}<\/pre>\n<p>There&#8217;s probably a lot of room for improvement for this code but the goal of this blog post is to show you how handy PowerShell can be. It can help you save a lot of time.<br \/>\nI took about 1 hour to write this code and I&#8217;m sure my customer will save more than that every month.<\/p>\n\n\n<p>Written by <a href=\"https:\/\/www.linkedin.com\/in\/steven-naudet-aa540158\/\">Steven Naudet<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post, I will share with you a small PowerShell script I did recently. I have noticed that my customer performs a very repetitive and time-consuming task almost every day. New columns are added to tables on their business-critical database and they need to maintain SQL scripts file with all the ALTER TABLE [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,99],"tags":[2550,2136],"type_dbi":[],"class_list":["post-14900","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-sql-server","tag-sql-server-2","tag-template-file"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server: Generating SQL script using PowerShell and Template file - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server: Generating SQL script using PowerShell and Template file\" \/>\n<meta property=\"og:description\" content=\"In this blog post, I will share with you a small PowerShell script I did recently. I have noticed that my customer performs a very repetitive and time-consuming task almost every day. New columns are added to tables on their business-critical database and they need to maintain SQL scripts file with all the ALTER TABLE [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-18T14:46:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-01T09:47:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server: Generating SQL script using PowerShell and Template file\",\"datePublished\":\"2020-10-18T14:46:58+00:00\",\"dateModified\":\"2025-10-01T09:47:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\"},\"wordCount\":283,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif\",\"keywords\":[\"SQL Server\",\"Template file\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\",\"name\":\"SQL Server: Generating SQL script using PowerShell and Template file - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif\",\"datePublished\":\"2020-10-18T14:46:58+00:00\",\"dateModified\":\"2025-10-01T09:47:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage\",\"url\":\"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif\",\"contentUrl\":\"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server: Generating SQL script using PowerShell and Template file\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server: Generating SQL script using PowerShell and Template file - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server: Generating SQL script using PowerShell and Template file","og_description":"In this blog post, I will share with you a small PowerShell script I did recently. I have noticed that my customer performs a very repetitive and time-consuming task almost every day. New columns are added to tables on their business-critical database and they need to maintain SQL scripts file with all the ALTER TABLE [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/","og_site_name":"dbi Blog","article_published_time":"2020-10-18T14:46:58+00:00","article_modified_time":"2025-10-01T09:47:48+00:00","og_image":[{"url":"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif","type":"","width":"","height":""}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server: Generating SQL script using PowerShell and Template file","datePublished":"2020-10-18T14:46:58+00:00","dateModified":"2025-10-01T09:47:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/"},"wordCount":283,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage"},"thumbnailUrl":"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif","keywords":["SQL Server","Template file"],"articleSection":["Database Administration &amp; Monitoring","Database management","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/","name":"SQL Server: Generating SQL script using PowerShell and Template file - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage"},"thumbnailUrl":"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif","datePublished":"2020-10-18T14:46:58+00:00","dateModified":"2025-10-01T09:47:48+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#primaryimage","url":"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif","contentUrl":"https:\/\/s8.gifyu.com\/images\/New-AddColumnSQL.gif"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-generating-sql-script-using-powershell-and-template-file\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server: Generating SQL script using PowerShell and Template file"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14900","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14900"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14900\/revisions"}],"predecessor-version":[{"id":40595,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14900\/revisions\/40595"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14900"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14900"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14900"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14900"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}