Using Powershell to not script database collations for columns

Published 19 November 10 10:10 PM | MartinBell

If you use the SMO scripting object to script a table as I did in my previous article on Powershell for the following script which scripts out the Person.Contact table in the Adventureworks database:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem | Where {$_.Schema -eq "Person" -And $_.Name -eq "Contact" } ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}


You will get the following file produced:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[Contact]') AND type in (N'U'))
DROP TABLE [Person].[Contact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Contact](
    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
    [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    [EmailPromotion] [int] NOT NULL,
    [Phone] [dbo].[Phone] NULL,
    [PasswordHash] [varchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
    [PasswordSalt] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
    [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

One thing I am not keen on is that all varchar columns are given a collation. This can be an issue if you are wanting to use the database collation for the column as this is effectively hard coding them.

If you add the following line to the code:

$Scripter.Options.NoCollation=$True;

Then no collations at all are produced including the ones that are set to a specific collation that is different to the database default.

So how do you get around this and remove the database specific collations?

A simple solution would be to take the code from this Scripting Guy article and just re-parse the file:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem | Where {$_.Schema -eq "Person" -And $_.Name -eq "Contact" } ) {
$FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.FileName=$FileName
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
(Get-Content $FileName) |
Foreach-Object {$_ -replace " COLLATE Latin1_General_CI_AS", ""} |
Set-Content $FileName
}

I don’t think this is a great solution as it re-reads the whole file and writes it out for a second time. I’ve also hard coded the collation to be substituted, and I can get the database collation from a database object. So I decided to modify the Books Online example and here is what I came up with:

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("AdventureWorks")
$collation = $db.collation
$scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
$scrp.Options.DriAll=$False
$scrp.Options.IncludeHeaders=$False
$scrp.Options.ToFileOnly=$False
$scrp.Options.WithDependencies=$False
$tb = New-Object Microsoft.SqlServer.Management.Smo.Table
foreach ($tb in $db.Tables | Where {$_.Schema -eq "Person" -And $_.Name -eq "Contact" } )
{
   if ($tb.IsSystemObject -eq $FALSE)
   {
      $scrp.Options.IncludeIfNotExists=$True; 
      $scrp.Options.ScriptDrops=$True
      $filename="C:\PowerShell\" + $tb.Schema + "_" + $tb.Name + ".sql"
      $header="/*`r`nFile: " + $filename  + "`r`nObject: " + $tb.Schema + "." + $tb.Name  + "`r`nCreated: " + (Get-Date).ToString() + "`r`n*/`r`n"
      out-file $filename -Encoding ASCII -InputOBJECT $header
      $sc=$scrp.Script($tb)
      $sc+="GO`r`n"
      $scrp.Options.ScriptDrops=$False; 
      $scrp.Options.IncludeIfNotExists=$False; 
      $sc+=$scrp.Script($tb)
      $sc+="GO"
      foreach ($st in $sc )
      {
           $st.replace(" COLLATE " + $collation,"")  | out-file $filename -Append -Encoding ASCII
      }
   }
}

One of the issues I encountered was that I did not get any delimiters (GOs!) when I don’t write it to file, so I have added my own! The carriage returns and newlines ("`r`n") have also been added. I use the out-file cmdlet to write to the file, but I need to use the –Append option otherwise only the final line will be in the file. The –Append option requires the file to already exist, I have added a file header be written when the file is created. The output is as follows:

/*
File: C:\PowerShell\Person_Contact.sql
Object: Person.Contact
Created: 19/11/2010 21:56:58
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[Contact]') AND type in (N'U'))
DROP TABLE [Person].[Contact]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Contact](
 [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [EmailPromotion] [int] NOT NULL,
 [Phone] [dbo].[Phone] NULL,
 [PasswordHash] [varchar](128) NOT NULL,
 [PasswordSalt] [varchar](10) NOT NULL,
 [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


 




Filed under: , ,

Comments

No Comments

This Blog

SQL Blogs

Syndication