How to script non-default collation and skip expli

Question

In SSMS 2008 R2, I created a table:

aTest(Albnian varchar(10), Dflt varchar(10))

In SSMS table designer, both columns have the collation: "<database default>" (under "Column Properties" → "Table Designer")

I changed the collation of the column "Albnian" to a non-default, for example, Albanian_CI_AS.

If I script the table in SSMS (right click on "aTest" → "Script Tables as" → "CREATE To" → "New Query Editor Window", I get [1] with no explicit collations scripted at all.

Bad.

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

In SSMS menu → Tools → Options → SQL Server Object Explorer → Scripting, I changed:

• Include collation: True
• Script defaults: False

but now, I am getting all column collations scripted, both default and non-default ones [2].

How can I configure script generation to script non-default collation and skip the default ones, as 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]

Related question:

• What issues to anticipate having different collations between development and production SQL Servers?

Answer 1

I submitted a suggestion through MS Connect:
Collations are scripted either for all columns or for none

Here's the related response:

Posted by Microsoft on 11/11/2010 at 10:16 AM
Hi Gennady:

Thanks for writing in to Microsoft. We greatly value your feedback. We understand your problem, and how fixing this could improve productivity.

However, given the work that would be involved in implementing this work, and our set of deliverables, we do not think, we would be able to get to this in the near future.

Having said that, we value your suggestions, and would like to assure you that, we would keep these ideas in mind, when we do revisit this feature in the future.

Thanks again for providing feedback and making SQL Server the greatest Database server.

Regards

Chandramouli