maintenance/mssql/archives/patch-logging-drop-fks.sql

   1 DECLARE @base nvarchar(max),
   2         @SQL nvarchar(max),
   3         @id sysname;--
   4
   5 SET @base = 'ALTER TABLE /*_*/logging DROP CONSTRAINT ';--
   6
   7 SELECT @id = fk.name
   8 FROM sys.foreign_keys fk
   9 JOIN sys.foreign_key_columns fkc
  10         ON fkc.constraint_object_id = fk.object_id
  11 JOIN sys.columns c
  12         ON c.column_id = fkc.parent_column_id
  13         AND c.object_id = fkc.parent_object_id
  14 WHERE
  15         fk.parent_object_id = OBJECT_ID('/*_*/logging')
  16         AND fk.referenced_object_id = OBJECT_ID('/*_*/mwuser')
  17         AND c.name = 'log_user';--
  18
  19 SET @SQL = @base + @id;--
  20
  21 EXEC sp_executesql @SQL;--
  22
  23 SELECT @id = fk.name
  24 FROM sys.foreign_keys fk
  25 JOIN sys.foreign_key_columns fkc
  26         ON fkc.constraint_object_id = fk.object_id
  27 JOIN sys.columns c
  28         ON c.column_id = fkc.parent_column_id
  29         AND c.object_id = fkc.parent_object_id
  30 WHERE
  31         fk.parent_object_id = OBJECT_ID('/*_*/logging')
  32         AND fk.referenced_object_id = OBJECT_ID('/*_*/page')
  33         AND c.name = 'log_page';--
  34
  35 SET @SQL = @base + @id;--
  36
  37 EXEC sp_executesql @SQL;