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

小开

INFORMATION _ SCHEMA. COLUMNS 的 COLUMN _ DEFAULT 列是您要查找的吗？

小开

我不认为它在 INFORATION _ SCHEMA 中-您可能必须使用 sysoobject 或相关的不推荐的表/视图。

您可能认为在 INFORATION _ SCHEMA. TABLE _ CONSTRINTS 中应该有这样的类型，但是我没有看到。

小开

可能是因为在其他一些 SQL DBMS 中，“默认约束”并不是真正的约束，所以在“ INFORATION _ SCHEMA”中找不到它的名称。因此，最好的选择是“ INFORMATION _ SCHEMA”。正如其他人已经提到的。

(SQLServer- 无知者在此)

当您必须知道“缺省约束”的名称时，我能想到的唯一原因是 SQLServer 不支持 "ALTER TABLE xxx ALTER COLUMN yyy SET DEFAULT..."命令。但是，你已经在一个非标准的区域，你必须使用产品特定的方式来获得你需要的。

小开

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

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

小开

通过指定默认约束相关的表名和列名，可以使用以下方法进一步缩小结果范围:

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'

小开

如何使用 CHECK _ CONSTRINTS 和 CONSTRINT _ COLUMN _ USAGE 的组合:

    select columns.table_name,columns.column_name,columns.column_default,checks.constraint_name
from information_schema.columns columns
inner join information_schema.constraint_column_usage usage on
columns.column_name = usage.column_name and columns.table_name = usage.table_name
inner join information_schema.check_constraints checks on usage.constraint_name = checks.constraint_name
where columns.column_default is not null

小开

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

小开

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

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

小开

最佳答案

据我所知，默认值约束不是 ISO 标准的一部分，所以它们不会出现在 INFORATION _ SCHEMA 中。INFORATION _ SCHEMA 似乎是这类任务的最佳选择，因为它是跨平台的，但是如果信息不可用，应该使用对象目录视图(sys。 *)而不是系统表视图，后者在 SQL Server 2005及更高版本中已经不推荐使用了。

下面就是@user186476的回答。它返回给定列的默认值约束的名称。(对于非 SQL Server 用户，需要使用默认名称才能删除它，如果不自己命名默认约束，SQL Server 会创建一些疯狂的名称，比如“ DF _ TableN _ Column _ 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'

小开

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

这里有一个未来证明的答案，它根本不使用 sysobjects或其他 sys表:

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

小开

我使用以下脚本检索所有默认值(sp _ binddefault)和所有默认约束:

SELECT
t.name AS TableName, c.name AS ColumnName, SC.COLUMN_DEFAULT AS DefaultValue, dc.name AS DefaultConstraintName
FROM
sys.all_columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN INFORMATION_SCHEMA.COLUMNS SC ON (SC.TABLE_NAME = t.name AND SC.COLUMN_NAME = c.name)
WHERE
SC.COLUMN_DEFAULT IS NOT NULL
--WHERE t.name = '' and c.name = ''

小开

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

小开

对象目录视图 : sys.default _ 

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

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;

小开

巫师。
如果只需要检查默认约束是否存在
(缺省约束在管理不善的 DBs 中可能有不同的名称) ,
使用 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

最后，您可以创建一个名为 < br/> INFORATION _ SCHEMA. DEFAULT _ CONSTRINTS:

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

小开

一个更干净的方法:

SELECT DC.[name]
FROM [sys].[default_constraints] AS DC
WHERE DC.[parent_object_id] = OBJECT_ID('[Schema].[TableName]')

小开

如果目标数据库有，比如说，超过1M 个对象，那么使用 sys.default_constraints可以扫描 sys.syscolpars的90% 以上，然后对 dflt进行键查找，而你可能并不关心这些。在我的数据库上，从实际扫描的 剩余输入输出受损1.12 MM 行的158个读数中组装4行只需要1.129秒。

更改为使用当前 sys% tables/views，使用@Tim 的查询，以2ms 获得相同的4个约束。希望有人发现这个和我发现蒂姆的一样有用:

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;