Skip to main content

Scripting ALL ForeignKeys At Once!

I have a pretty extensive ETL in place involving a ton of tables. To improve performance and keep the transaction log clean, I recently decided to TRUNCATE a bunch of tables instead of DELETE. Unfortunately, Now, I was unable to truncate data as these tables are a part of foreign key based referential integrity in MS SQL.

No love lost. I started scripting one FK at a time and saving them in two separate files, DROP_FK.sql and RECREATE_FK.sql. After doing about five of them at 11pm, I realized that this was going to be a painful task. A light bulb went off - why not use the system tables; they surely have all of this information stored somewhere!

After a bit of research, I came up with the below query. There are many other queries (probably more robust than mine) out there on the interwebs! Please do your research.

Without further ado, here is my query.

Database: any
Tables: sys.tables
      sys.schemas
      sys.foreign_keys
      sys.columns
      sys.foreign_key_columns


DROP CONSTRAINTS

SELECT convert(Varchar(max),'ALTER TABLE ' + SS.NAME + '.[' + ST.NAME + ']           DROP CONSTRAINT [' + FK.NAME + ']') as DropFK_Script
INTO #DropFK
FROM SYS.FOREIGN_KEYS FK
INNER JOIN SYS.TABLES ST ON FK.PARENT_OBJECT_ID = ST.OBJECT_ID
INNER JOIN SYS.SCHEMAS SS ON ST.schema_id = SS.schema_id

 select * from #dropfk
drop table #dropfk



RECREATE CONSTRAINTS

SELECT convert(Varchar(max),'ALTER TABLE ' + SS_cons.NAME + '.[' + ST_cons.NAME + ']          ADD CONSTRAINT [' + FK.NAME + ']          FOREIGN KEY([' + 

 STUFF((SELECT ',' + c.name
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fc ON fc.parent_column_id = c.column_id 
AND fc.parent_object_id = c.[object_id]
    WHERE fc.constraint_object_id = fk.[object_id]
    ), 1, 1, N'')

 + '])          REFERENCES [' + SS_REF.name + '].[' + ST_ref.name + ']([' + 

 STUFF((SELECT ',' + c.name
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fc ON fc.referenced_column_id = c.column_id
AND fc.referenced_object_id = c.[object_id]
    WHERE fc.constraint_object_id = fk.[object_id]
    ), 1, 1, N'')
  + '])' )
as RecreateFK_Script
INTO #RecreateFK
FROM SYS.FOREIGN_KEYS FK
INNER JOIN SYS.TABLES ST_cons ON FK.PARENT_OBJECT_ID = ST_CONS.OBJECT_ID
INNER JOIN SYS.SCHEMAS SS_cons ON ST_CONS.schema_id = SS_cons.schema_id
INNER JOIN SYS.TABLES ST_ref ON FK.referenced_object_id = ST_ref.object_id
INNER JOIN SYS.SCHEMAS SS_ref ON ST_ref.SCHEMA_ID = SS_ref.schema_id
WHERE ST_REF.is_ms_shipped = 0 AND ST_CONS.is_ms_shipped = 0
ORDER BY SS_cons.NAME + '.[' + ST_cons.NAME + ']'

 select * from #RecreateFK
drop table #RecreateFK



 Credit: I do not have the URL handy, but the best example I found while researching was written by Aron Bertrand. 
Labels:

Comments

Post a Comment

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...
25 comments
Read more

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...
5 comments
Read more

cannot create a column accessor for OLE DB provider "ibmdasql" for linked server

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.
Post a Comment
Read more