Revision history [back]

Revision 2

updated 2018-07-08 10:35:31 +0200

يحدث هذا بالنسبة لقواعد البيانات القديمة فقط ..
ويكون الحل من خلال تشغيل جملة الـ SQL التالية بقاعدة البيانات .. ثم نسخ نتائجها (النتيجة عبارة عن جملة SQL) وتشغيلها بصفحة أخرى لنفس قاعدة البيانات.

with x as (
SELECT  obj.name AS FK_NAME,
   sch.name AS [schema_name],
   tab1.name AS [table],
   col1.name AS [column],
   tab2.name AS [referenced_table],
   col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
   ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
   ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
   ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
   ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
   ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
   ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
)
select 'alter table '+[table]+' drop constraint '+FK_NAME from x where referenced_table = 'account' and [table] in ( 'invitem','Contractor','AccountBalance','LedgerTransLine','Customer','Supplier','ThirdParty')

Revision 1

Ahmed Farouk

answered 2018-07-07 11:16:34 +0200

with x as ( SELECT obj.name AS FK_NAME, FKNAME, sch.name AS [schema_name], [schemaname], tab1.name AS [table], col1.name AS [column], tab2.name AS [referenced_table], [referencedtable], col2.name AS [referenced_column] [referencedcolumn] FROM sys.foreign_key_columns sys.foreignkeycolumns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id obj.objectid = fkc.constraintobjectid INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id tab1.objectid = fkc.parentobjectid INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id tab1.schemaid = sch.schemaid INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id col1.columnid = parentcolumnid AND col1.object_id = tab1.object_id col1.objectid = tab1.objectid INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id tab2.objectid = fkc.referencedobjectid INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id col2.columnid = referencedcolumnid AND col2.object_id = tab2.object_id col2.objectid = tab2.objectid ) select 'alter table '+[table]+' drop constraint '+FK_NAME '+FKNAME from x where referenced_table = 'account' and [table] in ( 'invitem','Contractor','AccountBalance','LedgerTransLine','Customer','Supplier','ThirdParty')

'invitem','Contractor','AccountBalance','LedgerTransLine','Customer','Supplier','ThirdParty')