fnDbVars - get database variables from Markit EDM database

fnDbVars.sql

/**
summary:
  fnDbVars returns database variable names and values from a Markit EDM database
parameters:
  - name : variableName
    type: nvarchar(MAX)
    description: the name of the database variable you want returned
    ifNull: the function will return all database variables
author: Adam Roderick
examples:
  - SELECT * FROM fnDbVars(NULL)
  - SELECT * FROM fnDbVars('My Variable Name')
returns: (Name, Value) where Name is the database variable name, and Value is its value
**/
CREATE FUNCTION ajr.fnDbVars(
  @variableName nvarchar(MAX)
)
RETURNS TABLE AS RETURN (
  SELECT TOP 100 PERCENT * FROM (
    SELECT 
      FLATTENED_SOURCE.DATABASE_VARIABLES.value('(./Name)[1]', 'nvarchar(max)') as Name,
      FLATTENED_SOURCE.DATABASE_VARIABLES.value('(./Value)[1]', 'nvarchar(max)') as Value
    FROM CADIS_SYS.CO_GLOBAL G
      CROSS APPLY G.DEFINITION.nodes('/CadisXml/Content/Globals/nv/list/Item') as FLATTENED_SOURCE(DATABASE_VARIABLES)
    WHERE 
      G.NAME = 'Database Parameters'
  ) as VARS
  WHERE ISNULL(@variableName, Name) = Name
  ORDER BY Name
)