获取默认约束信息

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我正在尝试获取给定表的默认约束信息.我是这样写的:

I am trying to get the Default Constraints information for a given table. I wrote something like this:

SELECT  c.name ,
        col.name
FROM    Rem.sys.default_constraints c
        INNER JOIN Rem.sys.columns col ON col.default_object_id = c.object_id
        INNER JOIN Rem.sys.objects o ON o.object_id = c.parent_object_id
        INNER JOIN Rem.sys.schemas s ON s.schema_id = o.schema_id       
WHERE   s.name = 'dbo'
        AND o.name = 'Desk_Hist'

但它不会返回我的默认值.所以我尝试了不同的方式，查询是:

But it doesn't return me the default value. So I tried a different way and the query is:

SELECT *
                FROM    Rem.information_schema.columns columns
                WHERE   columns.table_catalog = 'Rem'
                        AND columns.table_schema = 'dbo'
                        AND columns.table_name = 'Desk_Hist'
                        AND COLUMN_DEFAULT IS NOT NULL

但它什么也没给我返回.

But it returns me nothing.

有人可以帮我吗?

推荐答案

默认值在 'definition' 列中，尝试在没有 where 的情况下运行查询:

Default value is in column 'definition', try to run query without where:

SELECT  c.name , 
        col.name, 
        c.definition
FROM    Rem.sys.default_constraints c 
        INNER JOIN Rem.sys.columns col ON col.default_object_id = c.object_id 
        INNER JOIN Rem.sys.objects o ON o.object_id = c.parent_object_id 
        INNER JOIN Rem.sys.schemas s ON s.schema_id = o.schema_id

相关文章