Database Collation Is Null

• SQL_Hunt

  SSC-Dedicated

  Points: 33486

  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: 469

  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