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?