Solved

Reset collation

Posted on 2006-11-23
4
922 Views
Last Modified: 2008-01-09
How can I programmatically (using TSQL) remove the collation sequence from all fields in a database, so that each field has collation sequence "database default" ?

At the moment most fields have a hard coded collation sequence which is different from the database default, and any fields added by the programs will default to the database default, so there's a collation mismatch on each where clause/join etc. I want to get them the same.

sql 2000

thanks
0
Comment
Question by:plq
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 500 total points
ID: 18003045
try:

declare
      @bolHasIdentity bit,
      @strIdentityColumn sysname,
      @strColName sysname,
      @strDataType varchar(50),
      @strPRECISION varchar(50),
      @intNullable smallint,
      @bolFirst bit,
      @strTable sysname,
      @strcollateSeq varchar(50)


set @strcollateSeq = 'SQL_Latin1_General_CP1_CI_AS' -- or whatever collation seq you want to use
set @strTable = 'bills' -- the name of your table

create table #tmpField
(
TABLE_QUALIFIER sysname, -- Table or view qualifier name. This field can be NULL.
TABLE_OWNER sysname, -- Table or view owner name. This field always returns a value.
TABLE_NAME sysname, -- Table or view name. This field always returns a value.
COLUMN_NAME sysname, -- Column name, for each column of the TABLE_NAME returned. This field always returns a value.
DATA_TYPE smallint, -- Integer code for ODBC data type. If this is a data type that cannot be mapped to an ODBC type, it is NULL. The native data type name is returned in the TYPE_NAME column.
[TYPE_NAME] varchar(50), -- String representing a data type. The underlying DBMS presents this data type name.
[PRECISION] int, -- Number of significant digits. The return value for the PRECISION column is in base 10.
LENGTH int, -- Transfer size of the data.1
SCALE smallint, -- Number of digits to the right of the decimal point.
RADIX smallint, -- Base for numeric datatypes.
NULLABLE smallint, -- Specifies nullability. 1 = NULL is possible. 0 = NOT NULL.
 
REMARKS varchar(254), -- This field always returns NULL.
COLUMN_DEF nvarchar(4000), -- Default value of the column.
SQL_DATA_TYPE smallint, -- Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB smallint, -- Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTH int, -- Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
ORDINAL_POSITION int, -- Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
IS_NULLABLE varchar(254), -- Nullability of the column in the table.
--                  ISO rules are followed to determine nullability.
--                  An ISO SQL-compliant DBMS cannot return an empty string.
--                  YES = Column can include NULLS.
--                  NO = Column cannot include NULLS.
--                  This column returns a zero-length string if nullability is unknown.
--                  The value returned for this column is different from the value returned for the NULLABLE column.
SS_DATA_TYPE tinyint -- SQL Server data type, used by extended stored procedures. For more information, see Data Types.  
)
insert into #tmpField exec sp_columns @table_name = @strTable

declare crsField cursor for
      select
            F.Column_Name,
            F.[Type_Name],
            ' (' + ltrim(str(F.[PRECISION])) + ')' Precisionx,
            F.Nullable
      from
            #tmpField F
      where
            charindex('char', F.[TYPE_NAME]) > 0
      order by
            Ordinal_Position

open crsField
set @bolFirst = 1

fetch next from crsField into
            @strColName,
            @strDataType,
            @strPRECISION,
            @intNullable
while @@Fetch_status = 0
begin
-- you could change the print command to exec
      print ('alter table ' + @strTable + ' Alter column ' + @strcolName + ' ' + @strDataType + @strprecision
        + ' COLLATE ' + @strCollateSEq + ' ' + case when @intNullable = 1 then ' Null ' else ' Not Null' end)

      fetch next from crsField into
                  @strColName,
                  @strDataType,
                  @strPRECISION,
                  @intNullable
end
close crsField
deallocate crsField
go
0
 
LVL 8

Author Comment

by:plq
ID: 18003132
This is excellent. Just one more minor issue ...

Any idea how to set to <database default> instead of a specific collation ?

     print ('alter table ' + @strTable + ' Alter column ' + @strcolName + ' ' + @strDataType + @strprecision
       + ' COLLATE ' + ????????????????????????? + ' ' + case when @intNullable = 1 then ' Null ' else ' Not Null' end)

0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 18003302
try changing it to:

 set @strcollateSeq = 'database_default'   -- or whatever collation seq you want to use

/Richard
0
 
LVL 8

Author Comment

by:plq
ID: 18003359
Excellent

Thanks for helping
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question