how to script non-default collation and skip explicit scripting for default collation?

In SSMS 2008 R2 I create the table aTest(Albnian varchar(10), Dflt varchar(10))

In SSMS table designer both column properties have Collation: < database default > (under Column Properties--> "Table designer")

I change collation of column Albnian to a non-default, for ex., to Albanian_CI_AS.

If I script the table in SSMS (right-click on aTest ---> Script Tables As ---> New Query Editor Window, I get [1] with no explicit collations scripted at all.

Bad. Obviously one would expect the table be scripted with explicit collation for non-default collation (the one developer intentionally introduced with a specific purpose) and no collation for default collation.

In SSMS menu Tools --> Options --> SQL Server Object Explorer --> Scripting -->
I change :
- Include collation: True
- Script defaults: false

but now I am getting all columns collations scripted, either default or non-default ones [2]

So, how can I configure script generation to script non-default collation and skip default one (like in [3])?

[1] Default scripting of table:

CREATE TABLE [dbo].[aTest]
(
    [Albnian] [varchar](10) NOT NULL,
    [Dflt] [varchar](10)  NOT NULL
) ON [PRIMARY]

[2] Table script after "Include collation" changed to True

CREATE TABLE [dbo].[aTest]
(
    [Albnian] [varchar](10) COLLATE Albanian_CI_AS NOT NULL,
    [Dflt] [varchar](10) COLLATE Cyrillic_General_CI_AS NOT NULL 
) ON [PRIMARY]

[3] Needed collation script generation behavior:

CREATE TABLE [dbo].[aTest]
(
    [Albnian] [varchar](10) COLLATE Albanian_CI_AS NOT NULL,
    --non-default should be scripted

    [Dflt] [varchar](10) NOT NULL 
    -- default database collation should not be scripted
) ON [PRIMARY]

ansaurus

tags:

views:

answers:

how to script non-default collation and skip explicit scripting for default collation?

related questions