API tools faq
paste
Guest User

Untitled

a guest
Jun 22nd, 2018
88
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.57 KB | None | 0 0
raw download clone embed print report
  1. select
  2. DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
  3. '].[' + ForeignKeys.ForeignTableName +
  4. '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
  5. , CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
  6. '].[' + ForeignKeys.ForeignTableName +
  7. '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName +
  8. '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn +
  9. ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  10. sys.objects.[name] + ']([' +
  11. sys.columns.[name] + ']) ON DELETE CASCADE; '
  12. from sys.objects
  13. inner join sys.columns
  14. on (sys.columns.[object_id] = sys.objects.[object_id])
  15. inner join (
  16. select sys.foreign_keys.[name] as ForeignKeyName
  17. ,schema_name(sys.objects.schema_id) as ForeignTableSchema
  18. ,sys.objects.[name] as ForeignTableName
  19. ,sys.columns.[name] as ForeignTableColumn
  20. ,sys.foreign_keys.referenced_object_id as referenced_object_id
  21. ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
  22. from sys.foreign_keys
  23. inner join sys.foreign_key_columns
  24. on (sys.foreign_key_columns.constraint_object_id
  25. = sys.foreign_keys.[object_id])
  26. inner join sys.objects
  27. on (sys.objects.[object_id]
  28. = sys.foreign_keys.parent_object_id)
  29. inner join sys.columns
  30. on (sys.columns.[object_id]
  31. = sys.objects.[object_id])
  32. and (sys.columns.column_id
  33. = sys.foreign_key_columns.parent_column_id)
  34. ) ForeignKeys
  35. on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
  36. and (ForeignKeys.referenced_column_id = sys.columns.column_id)
  37. where (sys.objects.[type] = 'U')
  38. and (sys.objects.[name] not in ('sysdiagrams'))
  39.  
  40. create function dbo.fk_columns (@constraint_object_id int)
  41. returns varchar(255)
  42. as begin
  43. declare @r varchar(255)
  44. select @r = coalesce(@r + ',', '') + c.name
  45. from sys.foreign_key_columns fkc
  46. join sys.columns c
  47. on fkc.parent_object_id = c.object_id
  48. and fkc.parent_column_id = c.column_id
  49. where fkc.constraint_object_id = @constraint_object_id
  50. return @r
  51. end
  52.  
  53. select distinct
  54. DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
  55. '].[' + ForeignKeys.ForeignTableName +
  56. '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + '] '
  57. , CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
  58. '].[' + ForeignKeys.ForeignTableName +
  59. '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName +
  60. '] FOREIGN KEY(' + dbo.fk_columns(constraint_object_id) + ')' +
  61. 'REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  62. sys.objects.[name] + '] '
  63. + ' ON DELETE CASCADE'
  64. from sys.objects
  65. inner join sys.columns
  66. on (sys.columns.[object_id] = sys.objects.[object_id])
  67. inner join (
  68. select sys.foreign_keys.[name] as ForeignKeyName
  69. ,schema_name(sys.objects.schema_id) as ForeignTableSchema
  70. ,sys.objects.[name] as ForeignTableName
  71. ,sys.columns.[name] as ForeignTableColumn
  72. ,sys.foreign_keys.referenced_object_id as referenced_object_id
  73. ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
  74. ,sys.foreign_keys.object_id as constraint_object_id
  75. from sys.foreign_keys
  76. inner join sys.foreign_key_columns
  77. on (sys.foreign_key_columns.constraint_object_id
  78. = sys.foreign_keys.[object_id])
  79. inner join sys.objects
  80. on (sys.objects.[object_id]
  81. = sys.foreign_keys.parent_object_id)
  82. inner join sys.columns
  83. on (sys.columns.[object_id]
  84. = sys.objects.[object_id])
  85. and (sys.columns.column_id
  86. = sys.foreign_key_columns.parent_column_id)
  87. -- Uncomment this if you want to include only FKs that already
  88. -- have a cascade constraint.
  89. -- where (delete_referential_action_desc = 'CASCADE' or update_referential_action_desc = 'CASCADE')
  90. ) ForeignKeys
  91. on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
  92. and (ForeignKeys.referenced_column_id = sys.columns.column_id)
  93. where (sys.objects.[type] = 'U')
  94. and (sys.objects.[name] not in ('sysdiagrams'))
  95.  
  96. ;WITH CTE AS
  97. (
  98. SELECT
  99. KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
  100. ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME
  101. ,KCU1.TABLE_NAME AS FK_TABLE_NAME
  102. ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
  103. ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
  104. ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
  105. ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME
  106. ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
  107. ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
  108. ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
  109. FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
  110.  
  111. INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
  112. ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
  113. AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
  114. AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
  115.  
  116. INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
  117. ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
  118. AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
  119. AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
  120. AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
  121. )
  122.  
  123.  
  124. SELECT
  125. FK_CONSTRAINT_NAME
  126. --,FK_SCHEMA_NAME
  127. --,FK_TABLE_NAME
  128. --,FK_COLUMN_NAME
  129. --,FK_ORDINAL_POSITION
  130. --,REFERENCED_CONSTRAINT_NAME
  131. --,REFERENCED_SCHEMA_NAME
  132. --,REFERENCED_TABLE_NAME
  133. --,REFERENCED_COLUMN_NAME
  134. --,REFERENCED_ORDINAL_POSITION
  135.  
  136. ,
  137. 'ALTER TABLE [' + FK_SCHEMA_NAME + ']'
  138. + '.[' + FK_TABLE_NAME + '] '
  139. + 'DROP CONSTRAINT [' + FK_CONSTRAINT_NAME + ']; '
  140. AS DropStmt
  141.  
  142. ,
  143. 'ALTER TABLE [' + FK_SCHEMA_NAME + ']'
  144. + '.[' + FK_TABLE_NAME + '] ' +
  145. + 'WITH CHECK ADD CONSTRAINT [' + FK_CONSTRAINT_NAME + '] '
  146. + 'FOREIGN KEY([' + FK_COLUMN_NAME + ']) '
  147. + 'REFERENCES [' + REFERENCED_SCHEMA_NAME + '].[' + REFERENCED_TABLE_NAME + ']([' + REFERENCED_COLUMN_NAME + ']) ON DELETE CASCADE; '
  148. AS CreateStmt
  149.  
  150. FROM CTE
  151.  
  152. WHERE (1=1)
  153. /*
  154. AND FK_TABLE_NAME IN
  155. (
  156. 'T_SYS_Geschossrechte'
  157. ,'T_SYS_Gebaeuderechte'
  158. ,'T_SYS_Standortrechte'
  159. )
  160.  
  161. AND REFERENCED_TABLE_NAME NOT LIKE 'T_AP_Ref_Mandant'
  162. */
  163.  
  164. ORDER BY
  165. FK_TABLE_NAME
  166. ,FK_CONSTRAINT_NAME
  167. ,FK_COLUMN_NAME
  168. ,FK_ORDINAL_POSITION
  169. ,REFERENCED_CONSTRAINT_NAME
  170. ,REFERENCED_TABLE_NAME
  171. ,REFERENCED_COLUMN_NAME
  172. ,REFERENCED_ORDINAL_POSITION
  173.  
  174. ;WITH CTE AS
  175. (
  176. SELECT
  177. KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
  178. ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME
  179. ,KCU1.TABLE_NAME AS FK_TABLE_NAME
  180. ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
  181. ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
  182. ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
  183. ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME
  184. ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
  185. ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
  186. ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
  187. FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
  188.  
  189. INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
  190. ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
  191. AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
  192. AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
  193.  
  194. INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
  195. ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
  196. AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
  197. AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
  198. AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
  199. )
  200. SELECT
  201. FK_SCHEMA_NAME
  202. ,FK_TABLE_NAME
  203. ,FK_CONSTRAINT_NAME
  204. --,FK_COLUMN_NAME
  205. --,REFERENCED_COLUMN_NAME
  206.  
  207.  
  208.  
  209. ,
  210. 'ALTER TABLE ' + QUOTENAME(FK_SCHEMA_NAME) + '.' + QUOTENAME(FK_TABLE_NAME) + ' '
  211. + 'DROP CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + '; '
  212. AS DropStmt
  213.  
  214. ,
  215. 'ALTER TABLE ' + QUOTENAME(FK_SCHEMA_NAME) + '.' + QUOTENAME(FK_TABLE_NAME) + '
  216. ADD CONSTRAINT ' + QUOTENAME(FK_CONSTRAINT_NAME) + '
  217. FOREIGN KEY('
  218. +
  219. SUBSTRING
  220. (
  221. (
  222. SELECT ', ' + QUOTENAME(FK.FK_COLUMN_NAME) AS [text()]
  223. FROM CTE AS FK
  224. WHERE FK.FK_CONSTRAINT_NAME = CTE.FK_CONSTRAINT_NAME
  225. AND FK.FK_SCHEMA_NAME = CTE.FK_SCHEMA_NAME
  226. AND FK.FK_TABLE_NAME = CTE.FK_TABLE_NAME
  227. FOR XML PATH, TYPE
  228. ).value('.[1]', 'nvarchar(MAX)')
  229. ,3, 4000
  230. )
  231. + ')
  232. '
  233. + ' REFERENCES ' + QUOTENAME(REFERENCED_SCHEMA_NAME) + '.' + QUOTENAME(REFERENCED_TABLE_NAME) + '('
  234. + SUBSTRING
  235. (
  236. (
  237. SELECT ', ' + QUOTENAME(Referenced.REFERENCED_COLUMN_NAME) AS [text()]
  238. FROM CTE AS Referenced
  239. WHERE Referenced.FK_CONSTRAINT_NAME = CTE.FK_CONSTRAINT_NAME
  240. AND Referenced.REFERENCED_SCHEMA_NAME = CTE.REFERENCED_SCHEMA_NAME
  241. AND Referenced.REFERENCED_TABLE_NAME = CTE.REFERENCED_TABLE_NAME
  242. FOR XML PATH, TYPE
  243. ).value('.[1]', 'nvarchar(MAX)')
  244. , 3, 4000
  245. )
  246. + ')
  247. ON DELETE CASCADE
  248. ; ' AS CreateStmt
  249.  
  250. FROM CTE
  251.  
  252. GROUP BY
  253. FK_SCHEMA_NAME
  254. ,FK_TABLE_NAME
  255. ,FK_CONSTRAINT_NAME
  256.  
  257. ,REFERENCED_SCHEMA_NAME
  258. ,REFERENCED_TABLE_NAME
  259.  
  260. ;WITH CTE AS
  261. (
  262. SELECT
  263. KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
  264. ,KCU1.TABLE_SCHEMA AS FK_SCHEMA_NAME
  265. ,KCU1.TABLE_NAME AS FK_TABLE_NAME
  266. ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
  267. ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
  268. ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
  269. ,KCU2.TABLE_SCHEMA AS REFERENCED_SCHEMA_NAME
  270. ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
  271. ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
  272. ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
  273. FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
  274.  
  275. INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
  276. ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
  277. AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
  278. AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
  279.  
  280. INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
  281. ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
  282. AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
  283. AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
  284. AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
  285. )
  286. SELECT
  287. FK_SCHEMA_NAME
  288. ,FK_TABLE_NAME
  289. ,FK_CONSTRAINT_NAME
  290. --,FK_COLUMN_NAME
  291. --,REFERENCED_COLUMN_NAME
  292.  
  293.  
  294.  
  295. ,
  296. 'ALTER TABLE ' || QUOTE_IDENT(FK_SCHEMA_NAME) || '.' || QUOTE_IDENT(FK_TABLE_NAME) || ' '
  297. || 'DROP CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || '; '
  298. AS DropStmt
  299.  
  300. ,
  301. 'ALTER TABLE ' || QUOTE_IDENT(FK_SCHEMA_NAME) || '.' || QUOTE_IDENT(FK_TABLE_NAME) || '
  302. ADD CONSTRAINT ' || QUOTE_IDENT(FK_CONSTRAINT_NAME) || '
  303. FOREIGN KEY(' || string_agg(FK_COLUMN_NAME, ', ') || ')
  304. '
  305. || ' REFERENCES ' || QUOTE_IDENT(REFERENCED_SCHEMA_NAME) || '.' || QUOTE_IDENT(REFERENCED_TABLE_NAME) || '(' || string_agg(REFERENCED_COLUMN_NAME, ', ') || ')
  306. ON DELETE CASCADE
  307. ; ' AS CreateStmt
  308.  
  309. FROM CTE
  310.  
  311. GROUP BY
  312. FK_SCHEMA_NAME
  313. ,FK_TABLE_NAME
  314. ,FK_CONSTRAINT_NAME
  315.  
  316. ,REFERENCED_SCHEMA_NAME
  317. ,REFERENCED_TABLE_NAME
  318.  
  319. ALTER TABLE emp DROP CONSTRAINT fk_dept;
  320.  
  321. ALTER TABLE emp ADD CONSTRAINT fk_dept
  322. FOREIGN KEY(dept_no)
  323. REFERENCES dept(deptno)
  324. ON DELETE CASCADE;
  325.  
  326. IF Object_id('tempdb..#queriesForContraints') IS NOT NULL
  327. BEGIN
  328. DROP TABLE #queriesForContraints
  329. END
  330.  
  331. DECLARE @ignoreTablesCommaSeparated VARCHAR(1000)
  332.  
  333. SELECT 'ALTER TABLE ['
  334. + ForeignKeys.foreigntableschema + '].['
  335. + ForeignKeys.foreigntablename
  336. + '] DROP CONSTRAINT ['
  337. + ForeignKeys.foreignkeyname + ']; '
  338. + 'ALTER TABLE ['
  339. + ForeignKeys.foreigntableschema + '].['
  340. + ForeignKeys.foreigntablename
  341. + '] WITH CHECK ADD CONSTRAINT ['
  342. + ForeignKeys.foreignkeyname
  343. + '] FOREIGN KEY(['
  344. + ForeignKeys.foreigntablecolumn
  345. + ']) REFERENCES ['
  346. + Schema_name(sys.objects.schema_id) + '].['
  347. + sys.objects.[name] + '](['
  348. + sys.columns.[name]
  349. + ']) ON DELETE CASCADE; ' AS query
  350. INTO #queriesForContraints
  351. FROM sys.objects
  352. INNER JOIN sys.columns
  353. ON ( sys.columns.[object_id] = sys.objects.[object_id] )
  354. INNER JOIN (SELECT sys.foreign_keys.[name] AS
  355. ForeignKeyName,
  356. Schema_name(sys.objects.schema_id) AS
  357. ForeignTableSchema,
  358. sys.objects.[name] AS
  359. ForeignTableName,
  360. sys.columns.[name] AS
  361. ForeignTableColumn,
  362. sys.foreign_keys.referenced_object_id AS
  363. referenced_object_id,
  364. sys.foreign_key_columns.referenced_column_id AS
  365. referenced_column_id
  366. FROM sys.foreign_keys
  367. INNER JOIN sys.foreign_key_columns
  368. ON (
  369. sys.foreign_key_columns.constraint_object_id =
  370. sys.foreign_keys.[object_id] )
  371. INNER JOIN sys.objects
  372. ON ( sys.objects.[object_id] =
  373. sys.foreign_keys.parent_object_id )
  374. INNER JOIN sys.columns
  375. ON ( sys.columns.[object_id] =
  376. sys.objects.[object_id] )
  377. AND ( sys.columns.column_id =
  378. sys.foreign_key_columns.parent_column_id ))
  379. ForeignKeys
  380. ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
  381. AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
  382. WHERE ( sys.objects.[type] = 'U' )
  383. AND ( sys.objects.[name] NOT IN (
  384. 'sysdiagrams' --add more comma separated table names here if required
  385. ) )
  386.  
  387. DECLARE @queryToRun NVARCHAR(MAX)
  388.  
  389. SELECT @queryToRun = STUFF(
  390. (SELECT query + ''
  391. FROM #queriesForContraints
  392. FOR XML PATH (''))
  393. , 1, 0, '')
  394.  
  395. EXEC sp_executesql @statement = @queryToRun
  396.  
  397. IF Object_id('tempdb..#queriesForContraints') IS NOT NULL
  398. BEGIN
  399. DROP TABLE #queriesForContraints
  400. END
  401.  
  402. EXEC sp_msforeachtable 'YourProcedureName ''?'''
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!