Search code examples
sqlsql-servert-sqlsql-server-2000collation

How to view the collation setting in SQL Server 2000?

I'm working with an ancient SQL Server 8.00.194, and I need to know the collation setting of the database and specific column.

I've naively tried to use documentation, but it seems that there were no such thing as sys.databases (same for sys.columns, sys.tables):

SELECT * FROM sys.databases

Msg 208, Level 16, State 1, Line 5
Invalid object name 'sys.databases'.

Also I have tried to view properties from UI with Management Studio, but it breaks all the time:

Cannot show requested dialog.

Failed to retrieve data for this request. (SqlManagerUI)

UPD. I kinda know that this is possible, because somebody has changed the collation on one column on this server, and that's why I'm doing this.

Solution

  • Yes, the metadata views have changed (or rather, first appeared) in SQL Server 2005. Before that, you could query the following:

    select * from master..sysdatabases;
select * from sysobjects;
select * from syscolumns;

    An easier way would be looking at the INFORMATION_SCHEMA views - these are ANSI standard and existed even in 2000, in almost the same form as they are now. In particular, INFORMATION_SCHEMA.COLUMNS shows column collation, that's for certain.