如何从MS Parallel Data Warehouse中提取列的默认值？

MS Parallel Data Warehouse 不支持object_definition函数，可通过关联sys.default_constraints系统视图实现需求，该视图的definition字段直接存储了默认约束的定义文本，无需调用不兼容的函数。

单字段默认值查询代码 #

SELECT dc.definition AS default_value_definition
FROM sys.columns c
INNER JOIN sys.default_constraints dc 
    ON c.default_object_id = dc.object_id
INNER JOIN sys.tables t 
    ON c.object_id = t.object_id
INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id
WHERE 
    c.name = 'col1' -- 替换为实际查询的列名
    AND t.name = 'tablename' -- 替换为实际查询的表名
    AND s.name = 'dbo' -- 替换为表所属的schema名称

全表所有列默认值查询代码 #

如果需要查询指定表全部列的默认值，去掉列名过滤条件即可：

SELECT 
    c.name AS column_name,
    dc.definition AS default_value_definition
FROM sys.columns c
LEFT JOIN sys.default_constraints dc 
    ON c.default_object_id = dc.object_id
INNER JOIN sys.tables t 
    ON c.object_id = t.object_id
INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id
WHERE 
    t.name = 'tablename'
    AND s.name = 'dbo'

内容的提问来源于stack exchange，提问作者Ondřej Mazanec