collation

please let me know how to check which collation it is set for.
thxServer Level:
SELECT SERVERPROPERTY ('Collation')
Database Level:
SELECT DATABASEPROPERTYEX('YourDatabase' , 'Collation' )
-Sue
On Mon, 2 Oct 2006 08:48:01 -0700, stoney
<stoney@.discussions.microsoft.com> wrote:

>please let me know how to check which collation it is set for.
>thx|||thx that is great
I know I can't change it at the server level unless I reinstall but can I
change it and the db level?
"Sue Hoegemeier" wrote:

> Server Level:
> SELECT SERVERPROPERTY ('Collation')
> Database Level:
> SELECT DATABASEPROPERTYEX('YourDatabase' , 'Collation' )
> -Sue
> On Mon, 2 Oct 2006 08:48:01 -0700, stoney
> <stoney@.discussions.microsoft.com> wrote:
>
>|||> I know I can't change it at the server level unless I reinstall
No need to reinstall, but you do need to rebuild the system databases.

> but can I
> change it and the db level?
Yes, with ALTER DATABASE. But that won't change collation for your existing
data. For that you need
to do ALTER TABLE ... ALTER COLUMN. Probably easier to export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:DFE231CB-16EA-4643-A3B1-7B9F7CB211C2@.microsoft.com...[vbcol=seagreen]
> thx that is great
> I know I can't change it at the server level unless I reinstall but can I
> change it and the db level?
> "Sue Hoegemeier" wrote:
>|||You can change a database collation with:
ALTER DATABASE YourDatabase
COLLATE CollationName
But that only affects new tables. So to really change the
collation and include all existing data and objects isn't
necessarily all that simple. You'd have to use
ALTER TABLE TableName
ALTER COLUMN...
and you have to drop index, constraints
etc. first and then recreate.
It's probably easiest to create a new database with the
appropriate collation and then use DTS or SSIS to copy the
objects and data over to the new database.
Either way, you'd want to allow for a good deal of testing
as well.
-Sue
On Mon, 2 Oct 2006 09:05:02 -0700, stoney
<stoney@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>thx that is great
>I know I can't change it at the server level unless I reinstall but can I
>change it and the db level?
>"Sue Hoegemeier" wrote:
>