42ama

Как пересоздать constraints таблицы

Nov 25th, 2021
380
6 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE #ReCreateConstraintsTable -- feel free to use a permanent table
  2. (
  3.   drop_script NVARCHAR(MAX),
  4.   create_script NVARCHAR(MAX)
  5. );
  6.  
  7. DECLARE @tableName NVARCHAR(50) = 'Call';
  8.  
  9. DECLARE @drop   NVARCHAR(MAX) = N'',
  10.         @create NVARCHAR(MAX) = N'';
  11.  
  12. -- drop is easy, just build a simple concatenated list from sys.foreign_keys:
  13. SELECT @drop += N'
  14. ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
  15.     + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
  16. FROM sys.foreign_keys AS fk
  17. INNER JOIN (select [object_id], [schema_id], [name] from sys.tables where [name] = @tableName) AS ct
  18.   ON fk.parent_object_id = ct.[object_id]
  19. INNER JOIN sys.schemas AS cs
  20.   ON ct.[schema_id] = cs.[schema_id];
  21.  
  22. INSERT #ReCreateConstraintsTable(drop_script) SELECT @drop;
  23.  
  24. -- create is a little more complex. We need to generate the list of
  25. -- columns on both sides of the constraint, even though in most cases
  26. -- there is only one column.
  27. SELECT @create += N'
  28. ALTER TABLE '
  29.    + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
  30.    + ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
  31.    + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
  32.    -- get all the columns in the constraint table
  33.     FROM sys.columns AS c
  34.     INNER JOIN sys.foreign_key_columns AS fkc
  35.     ON fkc.parent_column_id = c.column_id
  36.     AND fkc.parent_object_id = c.[object_id]
  37.     WHERE fkc.constraint_object_id = fk.[object_id]
  38.     ORDER BY fkc.constraint_column_id
  39.     FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  40.   + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  41.   + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
  42.    -- get all the referenced columns
  43.     FROM sys.columns AS c
  44.     INNER JOIN sys.foreign_key_columns AS fkc
  45.     ON fkc.referenced_column_id = c.column_id
  46.     AND fkc.referenced_object_id = c.[object_id]
  47.     WHERE fkc.constraint_object_id = fk.[object_id]
  48.     ORDER BY fkc.constraint_column_id
  49.     FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
  50. FROM sys.foreign_keys AS fk
  51. INNER JOIN sys.tables AS rt -- referenced table
  52.   ON fk.referenced_object_id = rt.[object_id]
  53. INNER JOIN sys.schemas AS rs
  54.   ON rt.[schema_id] = rs.[schema_id]
  55. INNER JOIN sys.tables AS ct -- constraint table
  56.   ON fk.parent_object_id = ct.[object_id]
  57. INNER JOIN sys.schemas AS cs
  58.   ON ct.[schema_id] = cs.[schema_id]
  59. WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0 AND ct.name = @tableName;
  60.  
  61. UPDATE #ReCreateConstraintsTable SET create_script = @create;
  62.  
  63. PRINT @drop;
  64. PRINT @create;
  65.  
  66. drop table #ReCreateConstraintsTable
  67. /*
  68. EXEC sp_executesql @drop
  69. -- clear out data etc. here
  70. EXEC sp_executesql @create;
  71. */
  72.  
RAW Paste Data