Collation for a database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Collation for a database

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-18 : 13:24:10

I know you can get the collation of a server with

SELECT @Col = CONVERT(varchar(255),SERVERPROPERTY('Collation'))

But how to get the collation of a database, while executing in another datbase.

I know I can use sp_helpdb...but the parsing is gonna be a drag.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 13:37:19

SELECT DATABASEPROPERTYEX( MyDB , 'Collation' )

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-18 : 13:44:58

Thank you very much..you need single quotes around the dbname though...




CREATE TABLE dbHELP (
	[name] nvarchar(24)
	, db_size nvarchar(13)
	, owner nvarchar(24)
	, dbid smallint
	, created char(11)
	, status varchar(340)
	, compatibility_level tinyint)
GO

INSERT INTO dbHELP ([name], db_size, owner, dbid, created, status, compatibility_level)
EXEC sp_HelpDB

SELECT CHARINDEX('Collation=',Status), CHARINDEX(',',Status,(CHARINDEX('Collation=',Status)))
, SUBSTRING(Status
	, CHARINDEX('Collation=',Status)+10
	, CHARINDEX(',',Status,(CHARINDEX('Collation=',Status)+10)) - CHARINDEX('Collation=',Status))
FROM dbHELP

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 16:26:42

"Thank you very much..you need single quotes around the dbname though"

No problem ... sorry about the quotes, lifted it from a


SELECT	[name],
	[Collation] = DATABASEPROPERTYEX(name, 'Collation')
FROM	master..sysdatabases

and didn't think about the [name] / 'MyDatabase' issue!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources