Database Collation Is Null

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  December 6, 2014 at 3:37 am

  #1763109

  Good question.

  Thanks.

• hot2use

  SSCrazy

  Points: 2915

  More actions

  January 13, 2015 at 5:14 am

  #1769763

  Apparently the definition of some columns in the sys.databases table is as follows:

  If a database is not ONLINE or AUTO_CLOSE is set to ON, the values of some columns may be NULL. If a database is OFFLINE, the corresponding row is not visible to low-privileged users. To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission or the CREATE DATABASE permission in the master database.

  and further down in BOL (SQL Server 2008 R2) it reads for the collation column:

  Collation for the database. Acts as the default collation in the database.

  NULL = Database is not online or AUTO_CLOSE is set to ON.

  Hope this helps anybody encountering the same problem as I had today.

  
  __________________________________
  Searching the KB articles is like
  picking your nose. You never know
  what you'll find.
  

• MK Morris

  SSC-Addicted

  Points: 492

  More actions

  December 10, 2018 at 1:46 pm

  #2015937

  Forgive me for replying to such an old post.  I came across it when searching for information about something I observed. 

  DATABASEPROPERTYEX(name, 'Collation') returns NULL  for a database if no tables have been created yet.  I didn't test if only tables are required or if other objects might cause the collation to appear.

  Here's my test -

  
USE master
GO

  --DROP DATABASE [TestDB181210]

  --Step 1 - Create a database
CREATE DATABASE [TestDB181210] ON PRIMARY
(
    NAME = N'TestDB181210'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\TestDB181210.mdf'
    ,SIZE = 10MB
)
LOG ON
(
    NAME = N'TestDB181210_log'
    ,FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\TestDB181210_log.ldf'
    ,SIZE = 5MB
)
COLLATE Latin1_General_BIN
WITH DB_CHAINING ON;

  ALTER AUTHORIZATION ON DATABASE::TestDB181210 TO sa;

  GO

  --Step 2 - Check collation
select name, state_desc, DATABASEPROPERTYEX(name, 'Collation')
from sys.databases

  --Step 3 - Create a table
USE TestDB181210
GO

  --Load with numbers
WITH t AS
(
  SELECT
        1 AS Col1
  UNION ALL
  SELECT
        Col1 + 1
  FROM t
  WHERE Col1 < 5
)
SELECT * INTO dbo.TestDB181210 FROM t where 1 = 2;

  --Step 4 - Check collation again
select name, state_desc, DATABASEPROPERTYEX(name, 'Collation')
from sys.databases