如何使用INFORMATION_SCHEMA查找默认约束？

Question

我想测试一个给定的默认约束是否存在。我不想使用sysobjects表，而是更标准的INFORMATION_SCHEMA。

我之前用过这个来检查表和主键约束，但是我没有在任何地方看到默认约束。

它们不存在吗？（我正在使用MS SQL Server 2000）。

编辑：我想要通过约束名称来获取。

- WildJoe

Answer 1

据我所知，默认值约束不是ISO标准的一部分，因此它们不会出现在INFORMATION_SCHEMA中。INFORMATION_SCHEMA似乎是这种任务的最佳选择，因为它是跨平台的，但如果信息不可用，则应使用对象目录视图（sys.*）而不是系统表视图，在SQL Server 2005及更高版本中已弃用。

以下基本上与@user186476的答案相同。它返回给定列的默认值约束名称。（对于非SQL Server用户，您需要该默认值的名称才能将其删除，如果您没有自己命名默认值约束，则SQL Server会创建类似于“DF_TableN_Colum_95AFE4B5”之类的疯狂名称。为了使未来更容易更改架构，请始终明确命名约束！）

-- returns name of a column's default value constraint 
SELECT
    default_constraints.name
FROM 
    sys.all_columns

        INNER JOIN
    sys.tables
        ON all_columns.object_id = tables.object_id

        INNER JOIN 
    sys.schemas
        ON tables.schema_id = schemas.schema_id

        INNER JOIN
    sys.default_constraints
        ON all_columns.default_object_id = default_constraints.object_id

WHERE 
        schemas.name = 'dbo'
    AND tables.name = 'tablename'
    AND all_columns.name = 'columnname'

- Robert Calhoun

---

注意：在不同的模式中可能会有相同的表名，因此您还应该在 sys.schemas 表上进行连接。 - Daniel James Bryars

@DanielJamesBryars 系统模式现已添加到查询中。 - Stephen Turner

请查看我的答案，它简洁易懂，在所有版本的SQL Server中都适用，不需要任何sys表，并且容易记忆。链接 - ErikE

@ErikE 你的代码假设默认约束的名称已知。这是一个容易解决的问题，正如你的代码所展示的那样。回答很好，但问题不对。 - DarLom

我的代码确实假设了这一点，因为这是提问者所要求的——“我想通过约束的名称来获取[特定默认约束是否存在]。” 我已经编辑了我的答案，使其直接回答问题的性质更加清晰。希望能有所帮助。 - ErikE

Answer 2

您可以通过指定默认约束所对应的表名和列名来进一步缩小结果范围，具体方法如下：

select * from sysobjects o 
inner join syscolumns c
on o.id = c.cdefault
inner join sysobjects t
on c.id = t.id
where o.xtype = 'D'
and c.name = 'Column_Name'
and t.name = 'Table_Name'

- Tim Lentine

---

我已经搜索了几个小时这个简单的查询。非常感谢！ - Samuel

应该设置 o.xtype = 'D' 才能在区分大小写的数据库中工作。 - IvanH

Answer 3

在Information_Schema视图中似乎没有默认约束名。

使用SELECT * FROM sysobjects WHERE xtype = 'D' AND name = @name按名称查找默认约束。

- devio

---

直接回答问题比后来的替代方案更好（SQL 2000和按约束名称查询）。 - Marc L.

只有当您知道约束名时，此代码才有效，但如果这是系统分配的... - T.S.

Answer 4

以下脚本列出了运行数据库中用户表的所有默认约束和默认值：

以下脚本列出了运行数据库中用户表的所有默认约束和默认值：

SELECT  
        b.name AS TABLE_NAME,
        d.name AS COLUMN_NAME,
        a.name AS CONSTRAINT_NAME,
        c.text AS DEFAULT_VALUE
FROM sys.sysobjects a INNER JOIN
        (SELECT name, id
         FROM sys.sysobjects 
         WHERE xtype = 'U') b on (a.parent_obj = b.id)
                      INNER JOIN sys.syscomments c ON (a.id = c.id)
                      INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)                                          
 WHERE a.xtype = 'D'        
 ORDER BY b.name, a.name

- Johan Badenhorst

Answer 5

如果您想通过列或表名获得约束，或者想获取数据库中的所有约束，请查看其他答案。然而，如果您只是想找到确切地回答问题所要求的内容，即“通过约束名称测试是否存在给定的默认约束”，那么有一个更简单的方法。

这是一个兼容未来的答案，它根本不使用sysobjects或其他sys表：

IF object_id('DF_CONSTRAINT_NAME', 'D') IS NOT NULL BEGIN
   -- constraint exists, work with it.
END

- ErikE

Answer 6

select c.name, col.name from sys.default_constraints c
    inner join sys.columns col on col.default_object_id = c.object_id
    inner join sys.objects o  on o.object_id = c.parent_object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
where s.name = @SchemaName and o.name = @TableName and col.name = @ColumnName

- user186476

---

多一些空格会更好，但这个代码使用了对象目录视图（sys.*）来完成原帖提出的问题，这是微软推荐的比向后兼容的系统表视图更好的方法。 - Robert Calhoun

Answer 7

INFORMATION_SCHEMA.COLUMNS中的COLUMN_DEFAULT列是否是您要查找的内容？

- user12861

---

是的和不是的，它告诉我有一个默认值以及它是什么，但我也需要约束的名称。 - WildJoe

此外，请注意，如果您的运行时 SQL 登录不拥有 dbo 模式，则在 COLUMN_DEFAULT 列中可能只会找到 NULL 值。 - Glen Little

Answer 8

死灵法师。
如果您只需要检查默认约束是否存在（在管理不善的数据库中，default-constraint可能具有不同的名称），
请使用INFORMATION_SCHEMA.COLUMNS（column_default）：

IF NOT EXISTS(
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (1=1) 
    AND TABLE_SCHEMA = 'dbo' 
    AND TABLE_NAME = 'T_VWS_PdfBibliothek' 
    AND COLUMN_NAME = 'PB_Text'
    AND COLUMN_DEFAULT IS NOT NULL  
)
BEGIN 
    EXECUTE('ALTER TABLE dbo.T_VWS_PdfBibliothek 
                ADD CONSTRAINT DF_T_VWS_PdfBibliothek_PB_Text DEFAULT (N''image'') FOR PB_Text; 
    '); 
END 

如果您只想通过约束名称进行检查：最初的回答

-- Alternative way: 
IF OBJECT_ID('DF_CONSTRAINT_NAME', 'D') IS NOT NULL 
BEGIN
    -- constraint exists, deal with it.
END 

最后但并非最不重要的，您可以创建一个名为INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS的视图：

CREATE VIEW INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS 
AS 
SELECT 
     DB_NAME() AS CONSTRAINT_CATALOG 
    ,csch.name AS CONSTRAINT_SCHEMA
    ,dc.name AS CONSTRAINT_NAME 
    ,DB_NAME() AS TABLE_CATALOG 
    ,sch.name AS TABLE_SCHEMA 
    ,syst.name AS TABLE_NAME 
    ,sysc.name AS COLUMN_NAME 
    ,COLUMNPROPERTY(sysc.object_id, sysc.name, 'ordinal') AS ORDINAL_POSITION 
    ,dc.type_desc AS CONSTRAINT_TYPE 
    ,dc.definition AS COLUMN_DEFAULT 

    -- ,dc.create_date 
    -- ,dc.modify_date 
FROM sys.columns AS sysc -- 46918 / 3892 with inner joins + where 
-- FROM sys.all_columns AS sysc -- 55429 / 3892 with inner joins + where 

INNER JOIN sys.tables AS syst 
    ON syst.object_id = sysc.object_id 

INNER JOIN sys.schemas AS sch
    ON sch.schema_id = syst.schema_id 

INNER JOIN sys.default_constraints AS dc 
    ON sysc.default_object_id = dc.object_id

INNER JOIN sys.schemas AS csch
    ON csch.schema_id = dc.schema_id 

WHERE (1=1) 
AND dc.is_ms_shipped = 0 

/*
WHERE (1=1) 
AND sch.name = 'dbo'
AND syst.name = 'tablename'
AND sysc.name = 'columnname'
*/

- Stefan Steiger

Answer 9

如果目标数据库有超过1M个对象，使用sys.default_constraints可能会导致扫描sys.syscolpars时占用90%以上的时间，然后进行关键查找以获取您可能不关心的dflt。在我的数据库中，仅从实际扫描的1.12MM行中读取158行需要1.129秒。

改为使用当前的sys.%表/视图，使用@Tim的查询，相同的4个约束条件可以在2毫秒内获取。希望有人能像我一样发现这对Tim很有用：

SELECT ConstraintName = sdc.name
     , SchemaName     = ssch.name
     , TableName      = stab.name
     , ColumnName     = scol.name
  FROM sys.objects            sdc
       INNER JOIN sys.columns scol
               ON scol.default_object_id = sdc.object_id
       INNER JOIN sys.objects stab
               ON stab.object_id         = scol.object_id
       INNER JOIN sys.schemas ssch
               ON ssch.schema_id         = stab.schema_id;

- SAinCA

Answer 10

WHILE EXISTS( 
    SELECT * FROM  sys.all_columns 
    INNER JOIN sys.tables ST  ON all_columns.object_id = ST.object_id
    INNER JOIN sys.schemas ON ST.schema_id = schemas.schema_id
    INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id
    WHERE 
    schemas.name = 'dbo'
    AND ST.name = 'MyTable'
)
BEGIN 
DECLARE @SQL NVARCHAR(MAX) = N'';

SET @SQL = (  SELECT TOP 1
     'ALTER TABLE ['+  schemas.name + '].[' + ST.name + '] DROP CONSTRAINT ' + default_constraints.name + ';'
   FROM 
      sys.all_columns

         INNER JOIN
      sys.tables ST
         ON all_columns.object_id = ST.object_id

         INNER JOIN 
      sys.schemas
         ON ST.schema_id = schemas.schema_id

         INNER JOIN
      sys.default_constraints
         ON all_columns.default_object_id = default_constraints.object_id

   WHERE 
         schemas.name = 'dbo'
      AND ST.name = 'MyTable'
      )
   PRINT @SQL
   EXECUTE sp_executesql @SQL 

   --End if Error 
   IF @@ERROR <> 0 
   BREAK
END 

- user3059720