我必須創建一個 T-SQL 腳本來更新列資料型別，但默認約束不允許這樣做，所以我必須首先洗掉約束然后更改列。
我試圖通過表列約束，但它不起作用。
SELECT
obj_table.NAME AS 'table',
columns.NAME AS 'column',
obj_Constraint.NAME AS 'constraint',
obj_Constraint.type AS 'type'
FROM
sys.objects obj_table
JOIN
sys.objects obj_Constraint ON obj_table.object_id = obj_Constraint.parent_object_id
JOIN
sys.sysconstraints constraints ON constraints.constid = obj_Constraint.object_id
JOIN
sys.columns columns ON columns.object_id = obj_table.object_id
AND columns.column_id = constraints.colid
AND columns.NAME = 'trans_reject_id'
WHERE
obj_table.NAME = 'abc_transaction_table'
ORDER BY
'table'
ALTER TABLE abc_transaction_table
DROP CONSTRAINT DF__abc_trans__ach_r__575DE8F7
任何幫助，將不勝感激。
uj5u.com熱心網友回復：
要更改列約束，您需要以這種方式撰寫查詢：
DECLARE @CONSTRAINT AS VARCHAR(255)
SET @CONSTRAINT = (SELECT obj_Constraint.NAME AS 'constraint'
FROM sys.objects obj_table
JOIN sys.objects obj_Constraint
ON obj_table.object_id = obj_Constraint.parent_object_id
JOIN sys.sysconstraints constraints
ON constraints.constid = obj_Constraint.object_id
JOIN sys.columns columns
ON columns.object_id = obj_table.object_id
AND columns.column_id = constraints.colid
WHERE obj_table.NAME='abc_transaction_table'
AND columns.Name = 'trans_reject_id'
)
DECLARE @REMOVE_CONSTARINT VARCHAR(255)
SET @REMOVE_CONSTARINT = 'ALTER TABLE abc_transaction_table DROP CONSTRAINT' @CONSTRAINT
EXEC(@REMOVE_CONSTARINT)
uj5u.com熱心網友回復：
嘗試這樣的事情
SELECT dc.name constraint_name,
schema_name(dc.schema_id) schema_name,
object_name(dc.parent_object_id) table_name,
c.name column_name
FROM sys.default_constraints dc
join sys.columns c
on dc.parent_column_id = c.column_id
and dc.parent_object_id = c.object_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/407729.html
標籤：
上一篇：如何清理RailsAPI引數
下一篇：如何計算蜂巢中的行數？