Database Field Collation settings (SchemaExplorer.ColumnSchemaCollection) & ExtendedProperties

G'day,

I've got a SQL Server 2005 database with mixed collation settings (2000 upgrade + some developer environmental differences caused the problem).

I can identify the collation settings via the following SQL Server Statement.

SELECT TABLE_CATALOG, TABLE_SCHEMA , TABLE_NAME,

COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

IS_NULLABLE, COLUMN_DEFAULT, COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS AS cols

WHERE (TABLE_CATALOG = N' yourdbnamegoeshere') AND (NUMERIC_PRECISION IS NULL)

AND NOT(COLLATION_NAME IS NULL)

ORDER BY TABLE_NAME, COLUMN_NAME

 

I created a simple little Codesmith template to find out if the extended properties contained the Collation_Name values.

<%@ CodeTemplate Language="VB" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>
<%@ Property Name="SourceSchema" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the code should be based on." %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%  

 Dim strDefaultValue as string = ""
 Dim objSortedCols As SchemaExplorer.ColumnSchemaCollection

    ' Sort the column in the table alphabetically, so we have consistent changes through all types of Databases.
 objSortedCols = New SchemaExplorer.ColumnSchemaCollection(SourceSchema.Columns)
    objSortedCols.Sort(New SchemaExplorer.PropertyComparer("Name"))
 
%>

<% For intLoop as integer = 0 to objSortedCols.Count -1 %>
[<%= SourceSchema.Name %>].[<% = objSortedCols.Item(intLoop).Name %>]
<%
 For intEPLoop as integer = 0 to objSortedCols.Item(intLoop).ExtendedProperties.count -1
%>   
 <% = objSortedCols.Item(intLoop).ExtendedProperties(intEPLoop).Name %> = <% = objSortedCols.Item(intLoop).ExtendedProperties(intEPLoop).Value %>
 <% Next intEPLoop %> 
<% Next intLoop %>

However in Codesmith 3.x there are no Collation extended properties for collation.  I search for a matching property, but could not find one.  How can I use codesmith to build an ALTER table script to make my field collation settings consistent?

Hello,

The following attached file should work. I have only tested it on an old database that I had lying around, so if you find a bug please post the fix.

Thanks

-Blake Niemyjski

Blake,

Thanks for the post.  The template fails when dealing with the following SQL 2005 fields.

• varchar -> forgets to set the fields size (or truncates)
• nvarchar(max) -> sets the field size to (-1).
• I aslo had issues with text() fields.

The template re-applied the collation order, even if the order matches, while I only wanted to fix non matching ones.

Finally the resulting script will also fail if any of the fields you want to change participate in relationships. I know this is poor DB practice, but customers still have these poorly designed databases, so I suspect that dropping relationships then recreating them will also be required for a complete version of the template.

I'm not a C# person, so it will take me a little time to fix the template.

Hello,

Theres a method in the sqlcode behind which one could reference that will return the correct field sizes. I didn't know about them at the time of writting this .  If you add anything to this template, besure to post updates to this post :) and I'll update it in the template share forum.

Thanks

-Blake Niemyjski