CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000collation
astef
astef

Reputation: 9498

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.

Upvotes: 0

Views: 726

Answers (2)

José Paulo Rocco
José Paulo Rocco

Reputation: 1

SELECT CONVERT (varchar(256), DATABASEPROPERTYEX('database_name','collation'));

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

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.

Upvotes: 2

Related Questions