SQL 如何使用 INFORMATION_SCHEMA 找到默认约束？

How do I find a default constraint using INFORMATION_SCHEMA?

提问by WildJoe

I'm trying to test if a given default constraint exists. I don't want to use the sysobjects table, but the more standard INFORMATION_SCHEMA.

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

I've used this to check for tables and primary key constraints before, but I don't see default constraints anywhere.

我以前用它来检查表和主键约束，但我没有在任何地方看到默认约束。

Are they not there? (I'm using MS SQL Server 2000).

他们不在吗？（我使用的是 MS SQL Server 2000）。

EDIT: I'm looking to get by the name of the constraint.

编辑：我希望得到约束的名称。

回答by Robert Calhoun

As I understand it, default value constraints aren't part of the ISO standard, so they don't appear in INFORMATION_SCHEMA. INFORMATION_SCHEMA seems like the best choice for this kind of task because it is cross-platform, but if the information isn't available one should use the object catalog views (sys.*) instead of system table views, which are deprecated in SQL Server 2005 and later.

据我了解，默认值约束不是 ISO 标准的一部分，因此它们不会出现在 INFORMATION_SCHEMA 中。INFORMATION_SCHEMA 似乎是此类任务的最佳选择，因为它是跨平台的，但如果信息不可用，则应使用对象目录视图 (sys.*) 而不是系统表视图，后者在 SQL Server 中已弃用2005 年及以后。

Below is pretty much the same as @user186476's answer. It returns the name of the default value constraint for a given column. (For non-SQL Server users, you need the name of the default in order to drop it, and if you don't name the default constraint yourself, SQL Server creates some crazy name like "DF_TableN_Colum_95AFE4B5". To make it easier to change your schema in the future, always explicitly name your constraints!)

以下与@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'

回答by Tim Lentine

You can use the following to narrow the results even more by specifying the Table Name and Column Name that the Default Constraint correlates to:

通过指定与默认约束相关的表名和列名，您可以使用以下内容进一步缩小结果范围：

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'

回答by devio

There seems to be no Default Constraint names in the Information_Schemaviews.

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

use SELECT * FROM sysobjects WHERE xtype = 'D' AND name = @nameto find a default constraint by name

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

回答by Johan Badenhorst

The script below lists all the default constraints and the default values for the user tables in the database in which it is being run:

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

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

回答by ErikE

If you want to get a constraint by the column or table names, or you want to get all the constraints in the database, look to other answers. However, if you're just looking for exactly what the question asks, namely, to "test if a given default constraint exists ... by the name of the constraint", then there's a much easier way.

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

Here's a future-proof answer that doesn't use the sysobjectsor other systables at all:

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

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

回答by ErikE

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

回答by user12861

Is the COLUMN_DEFAULT column of INFORMATION_SCHEMA.COLUMNS what you are looking for?

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

回答by user3059720

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 

回答by Stefan Steiger

Necromancing.
If you only need to check if a default-constraint exists
(default-constraint(s) may have different name in poorly-managed DBs),
use INFORMATION_SCHEMA.COLUMNS (column_default):

死灵法术。
如果您只需要检查是否存在默认约束
（默认约束在管理不善的数据库中可能有不同的名称），请
使用 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 

If you want to check by the constraint-name only:

如果您只想通过约束名称进行检查：

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

And last but not least, you can just create a view called
INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS:

最后但并非最不重要的是，您可以创建一个名为
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'
*/

回答by eigenharsha

Object Catalog View: sys.default_constraints

对象目录视图：sys.default_constraints

The information schema views INFORMATION_SCHEMAare ANSI-compliant, but the default constraints aren't a part of ISO standard. Microsoft SQL Server provides system catalog views for getting information about SQL Server object metadata.

信息模式视图INFORMATION_SCHEMA符合 ANSI，但默认约束不是 ISO 标准的一部分。Microsoft SQL Server 提供了用于获取有关 SQL Server 对象元数据的信息的系统目录视图。

sys.default_constraintssystem catalog view used to getting the information about default constraints.

sys.default_constraints用于获取有关默认约束的信息的系统目录视图。

SELECT so.object_id TableName,
       ss.name AS TableSchema,
       cc.name AS Name,
       cc.object_id AS ObjectID,              
       sc.name AS ColumnName,
       cc.parent_column_id AS ColumnID,
       cc.definition AS Defination,
       CONVERT(BIT,
               CASE cc.is_system_named
                   WHEN 1
                   THEN 1
                   ELSE 0
               END) AS IsSystemNamed,
       cc.create_date AS CreationDate,
       cc.modify_date AS LastModifiednDate
FROM sys.default_constraints cc WITH (NOLOCK)
     INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id = cc.parent_object_id
     LEFT JOIN sys.schemas ss WITH (NOLOCK) ON ss.schema_id = so.schema_id
     LEFT JOIN sys.columns sc WITH (NOLOCK) ON sc.column_id = cc.parent_column_id
                                               AND sc.object_id = cc.parent_object_id
ORDER BY so.name,
         cc.name;