我有这个 SQL 查询:
ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
但是显然,在我们使用的一些其他数据库中,该约束具有不同的名称。如何检查是否存在名称为FK_ChannelPlayerSkins_Channels
的约束。
我有这个 SQL 查询:
ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
但是显然,在我们使用的一些其他数据库中,该约束具有不同的名称。如何检查是否存在名称为FK_ChannelPlayerSkins_Channels
的约束。
INFORMATION_SCHEMA
是您的好朋友。它有各种视图,显示各种模式信息。检查您的系统视图。您将发现您有三个处理约束的视图,其中之一是 CHECK_CONSTRAINTS
。
我使用这个来检查和删除列上的约束。它应该包含你所需的一切。
DECLARE
@ps_TableName VARCHAR(300)
, @ps_ColumnName VARCHAR(300)
SET @ps_TableName = 'mytable'
SET @ps_ColumnName = 'mycolumn'
DECLARE c_ConsList CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT
'ALTER TABLE ' + RTRIM(tb.name) + ' drop constraint ' + sco.name AS csql
FROM
sys.Objects tb
INNER JOIN sys.Columns tc on (tb.Object_id = tc.object_id)
INNER JOIN sys.sysconstraints sc ON (tc.Object_ID = sc.id and tc.column_id = sc.colid)
INNER JOIN sys.objects sco ON (sc.Constid = sco.object_id)
where
tb.name=@ps_TableName
AND tc.name=@ps_ColumnName
OPEN c_ConsList
FETCH c_ConsList INTO @ls_SQL
WHILE (@@FETCH_STATUS = 0) BEGIN
IF RTRIM(ISNULL(@ls_SQL, '')) <> '' BEGIN
EXECUTE(@ls_SQL)
END
FETCH c_ConsList INTO @ls_SQL
END
CLOSE c_ConsList
DEALLOCATE c_ConsList
table_schema=DATABASE()
以下是我的函数,使用knex检查特定数据库和表中是否定义了特定的外键或索引。const isFKExists = async (knex, tableName, fkName) => {
const result = await knex.raw(
`SELECT COUNT(*) AS 'isExists' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_schema=DATABASE() AND table_name='${tableName}' AND CONSTRAINT_NAME='${fkName}' AND CONSTRAINT_TYPE = 'FOREIGN KEY'`
)
return (result[0][0].isExists === 1)
}
const isIndexExists = async (knex, tableName, indexName) => {
const result = await knex.raw(
`SELECT COUNT(*) AS 'isExists' FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='${tableName}' AND index_name='${indexName}'`
)
return (result[0][0].isExists > 0)
}
愉快编程!
SELECT tabla.name as Tabla,
restriccion.name as Restriccion,
restriccion.type as Tipo,
restriccion.type_desc as Tipo_Desc
FROM {DATABASE_NAME}.sys.objects tabla
INNER JOIN {DATABASE_NAME}.sys.objects restriccion
ON tabla.object_id = restriccion.parent_object_id
WHERE tabla.type = 'U' - Solo tablas creadas por el usuario.
AND restriccion.type = 'UQ' --Tipo de Restriccion UNIQUE
ORDER BY tabla.name, restriccion.type_desc
您可以使用上面的方法,但有一个注意事项:
IF EXISTS(
SELECT 1 FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')
AND name = 'CONSTRAINTNAME'
)
BEGIN
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME
END
name = [约束名称]
,因为一个表可能有多个外键,但仍然没有被检查的外键。