如何使用 INFORMATION_SCHEMA 找到默认约束? [英] How do I find a default constraint using INFORMATION_SCHEMA?

问题描述

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

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.

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

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

他们不在吗?(我使用的是 MS SQL Server 2000).

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

我希望得到约束的名称.

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

推荐答案

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

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.

以下与@user186476 的回答几乎相同.它返回给定列的默认值约束的名称.(对于非 SQL Server 用户，您需要默认名称才能删除它，如果您自己不命名默认约束，SQL Server 会创建一些疯狂的名称，例如DF_TableN_Colum_95AFE4B5".为了更容易更改您将来的架构，始终明确命名您的约束！)

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!)

-- 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'

这篇关于如何使用 INFORMATION_SCHEMA 找到默认约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！