October 20, 2015 at 3:38 pm
-- Here is a nice little utility that gives you more than the INFORMATION_SCHEMA.
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Use this to check foreign key constraints.
-- Create a USE <database>; GO for the database you need to check.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
SELECT
RFNGT.name AS 'Referencing Table',
RFNGT.object_id AS 'Referencing Object ID',
RFCDT.name AS 'Referenced Table Name',
RFCDC.object_id AS 'Referenced Table ID',
RFCDC.name AS 'Referenced Column Name',
FKC.name AS 'Foreign Key Name',
FKC.object_id AS 'Key ID Value',
FKC.type_desc
FROM
sys.tables RFNGT
JOIN
sys.foreign_keys FKC
ON
FKC.parent_object_id = RFNGT.object_id
JOIN
sys.tables RFCDT
ON
RFCDT.object_id = FKC.referenced_object_id
JOIN
sys.columns RFNGC
ON
RFNGC.object_id = RFNGT.object_id
JOIN
sys.columns RFCDC
ON
RFCDT.object_id = RFCDC.object_id
JOIN
sys.foreign_key_columns FKCC
ON
FKCC.constraint_object_id = FKC.object_id
AND
RFNGT.object_id = FKCC.parent_object_id
AND
RFNGC.column_id = FKCC.parent_column_id
AND
RFCDT.object_id = FKCC.referenced_object_id
AND
RFCDC.column_id = FKCC.referenced_column_id
JOIN
sys.schemas RFNGS
ON
RFNGS.schema_id = RFNGT.schema_id
JOIN
sys.schemas RFCDS
ON
RFCDS.schema_id = RFCDT.schema_id
ORDER BY 'Referencing Table' -- RFCDT.name
October 22, 2015 at 11:30 am
Or this:
SELECT
OBJECT_NAME(fkc.parent_object_id) AS [Referencing Table],
fkc.parent_object_id AS [Referencing Object ID],
OBJECT_NAME(fkc.referenced_object_id) AS [Referenced Table Name],
fkc.referenced_object_id AS [Referenced Table ID],
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS [Referenced Column Name],
OBJECT_NAME(fkc.constraint_object_id) AS [Foreign Key Name],
fkc.constraint_object_id AS [Key ID Value],
'FOREIGN_KEY_CONSTRAINT' AS type_desc
FROM sys.foreign_key_columns fkc
ORDER BY [Referencing Table], [Foreign Key Name]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 22, 2015 at 1:44 pm
Thanks Scott ... yours is much easier and simpler. No joins ... I need to look more at those specialized system tables! Good job.
October 22, 2015 at 6:28 pm
Now, here is a prototype FKC (Foreign Key Constraint) Remover / Rebuilder. It is not fully functional, but I will post updates, and those keeners out there must have a better way.
The Use Case helps in the final stages of DB Development, where a developer in the construction phase (Unified Process) can easily drop or re-create the existing FKC's (Foreign Key Contraints).
For me, I usually create or script out a completely separate code base or script to "initialize" and encapsulate all the FKC create scripts. Nowadays, I use the Database Project template in Visual Studio. Here is an example of a create script for one foreign key constraint:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'<Schema>.<ForeignKeyConstraintName>')
AND parent_object_id = OBJECT_ID(N'<Schema>.<TableName>'))
BEGIN
ALTER TABLE <Schema>.<TableName> WITH CHECK ADD CONSTRAINT
<DesiredForeignKeyName> FOREIGN KEY(ReferencingForeignKeyColumn) REFERENCES <ReferencedSchema>.<ReferencedTable>(<ReferencedForeignKeyColumnName>)
END
GO
-- Still to Do (updates welcome - even a different approach)
-- 1. Create a backup process for a coded FK Creator from existing code. Use Dynamic SQL.
-- 2. The loop is not checking against already deleted constraints.
-- 3. I need to create a table variable to hold the existing structures.
-- 4. Consume the table variable to hold the current information.
-- 5. Provide a parameter to rebuild or remove the keys with a backup.
-- Note: I know this is like publish / or Schema Compare, but this code should work.
-- Will run as a batch from here ...
IF OBJECT_ID(N'dbo.DropForeignKeys') IS NOT NULL -- Schema.Procedure
BEGIN
DROP PROCEDURE dbo.DropForeignKeys;
PRINT 'Stored Procedure dbo.DropForeignKeys was successfully Dropped'
END
GO
CREATE PROCEDURE dbo.DropForeignKeys
@SchemaTable_IN AS VARCHAR(50),
@Cycles AS TINYINT = 6,
@WithCodedBackup AS BIT = 0,
@CodedBackupSchema AS VARCHAR(40) = NULL,
@CodedBackupTable AS VARCHAR(40) = NULL,
@Messages_OUT AS VARCHAR(MAX) = 'Message on the OUTPUT Parameter' OUTPUT
AS
SET NOCOUNT ON
SET ANSI_NULLS ON -- NULL Comparison behaviour
SET QUOTED_IDENTIFIER ON -- delimter wrapping behaviour
-- Variables Section
DECLARE
@Remover AS VARCHAR(MAX),
@i AS TINYINT = 0,
@SchemaTable AS VARCHAR(50),
@Object AS INTEGER;
DECLARE @BackupCode AS TABLE (
ForeignKeySchemaPlusName VARCHAR(60),
ParentTableObjectName (50) VARCHAR(60),
ParentTableReferencedColumn
);
SET @SchemaTable = @SchemaTable_IN
SET @Object = OBJECT_ID(@SchemaTable)
PRINT CAST(@Object AS VARCHAR(15))
PRINT @SchemaTable
PRINT 'Cycles: ' + CAST( @Cycles AS VARCHAR(3))
-- Code Blocks
BEGIN TRY
WHILE @i <= @Cycles -- One of the tables has two FKCs
BEGIN
PRINT 'Entered Loop'
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = @Object)
BEGIN
SELECT @Remover ='ALTER TABLE ' + @SchemaTable + ' DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'
FROM sys.foreign_keys
WHERE parent_object_id = @Object
--EXECUTE (@Remover)
END
SET @i = @i + 1 -- + 1
END
END TRY
-- Error Trapping
BEGIN CATCH
SET @Messages_OUT = 'There was an error.'
END CATCH
GO
IF OBJECT_ID(N'dbo.DropForeignKeys') IS NOT NULL PRINT 'Stored Procedure dbo.DropForeignKeys was successfully Created.'
GO
-- Will run as a batch to here ...
-- Testing Harness follows.
-- To do:
-- 1. Add parameters (BITS) to rebuild or remove.
-- 2. Instead of Cycles (loop cycles), I want to use a TABLE variable to hold or build history.
-- 2. Test thoroughly and debug. I am currently using PRINT statements, but there is a better way.
DECLARE
@SchemaTable_IN VARCHAR(50), @ReturnValue_IN INTEGER, @Messages VARCHAR(MAX), @Cycles TINYINT
EXECUTE dbo.DropForeignKeys
@SchemaTable_IN = 'Know.PersonSoftwareJunction',
@Cycles = 6,
@Messages_OUT = @Messages OUTPUT
BEGIN;
PRINT 'Return Code: ' + CAST(@ReturnValue_IN AS VARCHAR(10))
PRINT ISNULL(@Messages, 'No Message')
END;
October 29, 2015 at 9:24 am
Looking at this again ... it's insane! But, it's a start.
October 29, 2015 at 9:28 am
I found the following script while google'ing the other day to script out the drop and creates of all the FK's in a DB.
Apologies to the source, I can't remember off the top of my head where I got the script from
CREATE TABLE #x -- feel free to use a permanent table
(
drop_script NVARCHAR(MAX),
create_script NVARCHAR(MAX)
);
DECLARE @drop NVARCHAR(MAX) = N'',
@create NVARCHAR(MAX) = N'';
-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
INSERT #x(drop_script) SELECT @drop;
-- create is a little more complex. We need to generate the list of
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
UPDATE #x SET create_script = @create;
PRINT @drop;
PRINT @create;
/*
EXEC sp_executesql @drop
-- clear out data etc. here
EXEC sp_executesql @create;
*/
Edit, my Google-Foo amazes me yet again, first link in my search
October 29, 2015 at 10:23 am
I'm curious. Why would you need to drop and recreate the Foreign Key constraints?
If it's for renaming purposes, I would simply rename them.
If it's for rebuilding the indexes, I would simply rebuild the indexes.
You could also check them if they were disabled, but if that's needed on a database level, I'm assuming a greater problem is behind this and we'd be only attacking the symptom instead of the root cause.
I used this for renaming purposes:
-------------------------------------------------------------------------------------------------
--The following script renames the constraints with the following naming convention:
-- Primary Keys: PK_TableName
-- Foreign Keys: FK_TableName_ReferencedTableName[_ColumnName] (Column name is only included when a table is referenced more than once)
-- Defaults: DF_TableName_ColumnName
-- Unique Constraints: UQ_TableName[_Num] (Adds a numeral if more than one UQs exist in a table)
-- Check Constraints: CK_TableName_ColumnName
-------------------------------------------------------------------------------------------------
DECLARE @SQL varchar(600);
DECLARE RenamingCur CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
----Foreign Keys
SELECT 'EXEC sp_rename ' +
QUOTENAME( f.name, '''') + ', ' +
QUOTENAME( 'FK_' + OBJECT_NAME( f.parent_object_id) + '_' + OBJECT_NAME( f.referenced_object_id)
+ CASE WHEN COUNT(*) OVER( PARTITION BY f.parent_object_id, f.referenced_object_id) > 1
THEN '_' + COL_NAME(fc.parent_object_id, fc.parent_column_id)
ELSE '' END, '''') + ', ''OBJECT'';'
FROM sys.foreign_keys f
JOIN sys.objects r ON f.referenced_object_id = r.object_id
JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
UNION ALL
----Defaults
SELECT 'EXEC sp_rename ' +
QUOTENAME( d.name, '''') + ', ' +
QUOTENAME( 'DF_' + OBJECT_NAME( d.parent_object_id) + '_' + COL_NAME(d.parent_object_id, d.parent_column_id), '''') + ', ''OBJECT'';'
FROM sys.default_constraints d
UNION ALL
----Primary Keys
SELECT 'EXEC sp_rename ' +
QUOTENAME( d.name, '''') + ', ' +
QUOTENAME( 'PK_' + OBJECT_NAME( d.parent_object_id), '''') + ', ''OBJECT'';'
FROM sys.key_constraints d
WHERE type = 'PK'
UNION ALL
----Unique Constraints
SELECT 'EXEC sp_rename ' +
QUOTENAME( d.name, '''') + ', ' +
QUOTENAME( 'UQ_' + OBJECT_NAME( d.parent_object_id)
+ ISNULL( NULLIF( '_' + CAST( ROW_NUMBER() OVER(PARTITION BY d.parent_object_id ORDER BY i.column_id) AS VARCHAR(3)), '1'), ''), '''')
+ ', ''OBJECT'';'
FROM sys.key_constraints d
JOIN sys.index_columns i ON d.parent_object_id = i.object_id AND d.unique_index_id = i.index_id AND key_ordinal = 1
WHERE d.type = 'UQ'
UNION ALL
----Check Constraints
SELECT 'EXEC sp_rename ' +
QUOTENAME( d.name, '''') + ', ' +
QUOTENAME( 'CK_' + OBJECT_NAME( d.parent_object_id) + '_' + COL_NAME(d.parent_object_id, d.parent_column_id), '''') + ', ''OBJECT'';'
FROM sys.check_constraints d;
OPEN RenamingCur;
FETCH NEXT FROM RenamingCur INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@SQL);
FETCH NEXT FROM RenamingCur INTO @SQL;
END
CLOSE RenamingCur;
DEALLOCATE RenamingCur;
--SELECT object_id,
-- type,
-- name
--FROM sys.objects
--WHERE type IN( 'C ', 'PK', 'UQ', 'F ', 'D ' )
October 29, 2015 at 10:44 am
Fwiw, here's my version of it, with an optional TRUNCATE (currently commented out) of the tables in between the DROP and ADD. I use this script, for example, to remove all data from tables so I'm left with only the structure after a RESTORE (into a dev or qa environment).
The results are very similar to the other script (edit: Anthony's posting), of course, except that:
(1) the table names are in alpha order and in ref name order within the same table
(2) it includes the ON DELETE and ON UPDATE options
(3) it includes the "NOT FOR REPLICATION" option, if specified in the original FK
SELECT
CASE WHEN constraint_action = 'TRUNCATE' THEN
'TRUNCATE TABLE [' + SCHEMA_NAME(o_par.schema_id) + '].[' + o_par.name + '] '
ELSE
'ALTER TABLE [' + SCHEMA_NAME(o_par.schema_id) + '].[' + o_par.name + '] ' +
constraint_action + ' CONSTRAINT [' + fk.name + '] ' +
CASE WHEN constraint_action = 'DROP' THEN ''
WHEN constraint_action = 'ADD' THEN
'FOREIGN KEY (' + SUBSTRING((
SELECT ', ' + COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
FROM sys.foreign_key_columns fkc
WHERE
fkc.constraint_object_id = fk.object_id
ORDER BY
fkc.constraint_column_id
FOR XML PATH('')
), 3, 4000) + ') ' +
'REFERENCES [' + SCHEMA_NAME(o_ref.schema_id) + '].[' + o_ref.name + '] (' + SUBSTRING((
SELECT ', ' + COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
FROM sys.foreign_key_columns fkc
WHERE
fkc.constraint_object_id = fk.object_id
ORDER BY
fkc.constraint_column_id
FOR XML PATH('')
), 3, 4000) + ') ' +
'ON DELETE ' + REPLACE(delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, '_', ' ') + ' ' +
'ON UPDATE ' + REPLACE(update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, '_', ' ') + ' ' +
CASE WHEN fk.is_not_for_replication = 1 THEN 'NOT FOR REPLICATION' ELSE '' END
END
END AS [--FK_DROP_TRUNC_ADD]
FROM sys.foreign_keys fk
INNER JOIN sys.objects o_par ON
o_par.object_id = fk.parent_object_id
INNER JOIN sys.objects o_ref ON
o_ref.object_id = fk.referenced_object_id
INNER JOIN (
SELECT 'DROP' AS constraint_action, 1 AS sort_sequence
--UNION ALL --uncomment these lines to also TRUNCATE the tables
--SELECT 'TRUNCATE', 2 --uncomment these lines to also TRUNCATE the tables
UNION ALL
SELECT 'ADD', 3
) AS constraint_controls ON
constraint_controls.constraint_action IN ('DROP', 'ADD') OR
fk.object_id = (SELECT MIN(fk2.object_id) FROM sys.foreign_keys fk2 WHERE fk2.parent_object_id = fk.parent_object_id)
WHERE
fk.is_ms_shipped = 0
ORDER BY
sort_sequence, o_par.name, o_ref.name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2015 at 11:03 am
Thanks Scott. The original use case had something to do with a logging harness for SSIS, which was spawned from a text book example based on an early WROX publication, written by that guru of all SSIS (I can find a better reference later). I will have to find the ultimate author to give more details on that. That whole technique has now replaced by the new SSISDB API (SQL). Essentially, I used a fully scripted SQL Solution that allowed me to develop the solution and test data loading with SSIS packages. The logging harness was completely disposable, in that the data was not really critical during the development phases. Dropping and re-creating the foreign key constraints just became part of my workflow. It's "baggage" that gets carried around until a better technique is found to do the containment of whatever is in the baggage.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy