Re: Need to reconfigure SQL Server to be Case-Insensitive

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 13:44:20 -0400

The short answer is that you cannot do that.

With ALTER TABLE you can change the collation of columns, but you will have
to script this for all affected columns. Also, there are some restrictions.
>From the BOL:

COLLATE < collation_name >

Specifies the new collation for the altered column. Collation name can be
either a Windows collation name or a SQL collation name. For a list and more
information, see Windows Collation Name and SQL Collation Name.

The COLLATE clause can be used to alter the collations only of columns of
the char, varchar, text, nchar, nvarchar, and ntext data types. If not
specified, the column is assigned the default collation of the database.

ALTER COLUMN cannot have a collation change if any of the following
conditions apply:

  a.. If a check constraint, foreign key constraint, or computed columns
reference the column changed.

  b.. If any index, statistics, or full-text index are created on the
column. Statistics created automatically on the column changed will be
dropped if the column collation is altered.

  c.. If a SCHEMABOUND view or function references the column.
Hope that helps,

Russell Fields

"str IP" <drasl@spam.aip.cc> wrote in message
news:6CEFEEFE-EC9D-498D-9865-A84A9C404C22@microsoft.com...
> But I have a lot of databases and many tables in each database. Can I
somehow change this setting in MSSQL and on every database and table.
> --
> Regards,
> str IP
>
>
> "Gregory A. Larsen" wrote:
>
> > The case-sensitivity of a table name is based on the collation setting
of
> > the database. If you change your database collation to Case-Insensitive
> > then when you create a new table then the name will be case-insensitive.
> >
> > --
> >
>
> -------------------------------------------------------------------------- 

--
>
> --------------------------------------------------------------------------
--
> > ----
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "str Ingi Ptursson" <drasl@w.dacoda.is> wrote in message
> > news:BCCD39DF-C296-45B8-B45C-3286DF08885F@microsoft.com...
> > > Can I somehow do that ?
> > >
> > > The table names are Case-Sensitive but I need them to Case-Insensitive
by
> > default.
> >
> >
> >

Relevant Pages

  • New bie to SQL, confused about SQL collation
    ... QUESTON # 1 ... I'm confused about the SQL collation, ... I'm making a content database for news management. ...
    (comp.databases.ms-sqlserver)
  • Re: Collation
    ... You can change collation for a column using ALTER TABLE... ... Database: Use the DATABASEPROPERTYEXfunction. ...
    (microsoft.public.sqlserver.server)
  • Re: casesencetive
    ... You can check out these pages within Books Online (right click on the ... You can change the collation of a column by using the ALTER TABLE statement: ... ALTER TABLE MyTable ALTER COLUMN CharCol ... > Outgoing mail is certified Virus Free. ...
    (microsoft.public.sqlserver.programming)
  • Re: Restoring database with different COLLATION
    ... > SET NOCOUNT ON ... > databases collation] ... > CREATE DATABASE TEST ... > the resultin ALTER TABLE statements, ...
    (microsoft.public.sqlserver.msde)
  • Re: Need to reconfigure SQL Server to be Case-Insensitive
    ... You will want to perform ALTER DATABASE and ALTER TABLE statements. ... You can change the collation of a column by using the ALTER TABLE statement: ... applied to all columns, user-defined data types, variables, and parameters ...
    (microsoft.public.sqlserver.server)