Database Collation Is Null

  • hot2use

    SSCrazy

    Points: 2915

    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

    Old Hand

    Points: 329

    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

Viewing 2 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply