Have you ever had the need to programmatically script foreign keys? If so, this script is for you. SSMS (SQL Server Management Studio) does not offer a simple solution to script foreign keys programmatically. In most cases the process of dropping and creating foreign keys is used to expedite bulk operations, synchronize foreign key constraints between two databases, and so on. I will provide a brief disclaimer that you should not drop all foreign key constrains, unless you really need to and understand the risks involved. Please plan appropriately to ensure the protection and integrity of your database.
I opted to use a TVF (table valued function) for this script. I used a TVF, so that I can pass input parameters. This function only allows the use of one input parameter, which allows the user to choose whether referential integrity should be checked before creating the indexes. The parameter is a “bit” data type and accepts a 1 or a 0. If the supplied value is 0, RI is not enforced when creating the foreign key. Likewise, a 1 tells the logic to check and enforce RI, for the foreign key.
I will be using the AdventureWorks database to demonstrate this code. You can find a copy of the database here: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
The code:
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_ScriptFKs]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN DROP FUNCTION [dbo].[fn_ScriptFKs] END GO /* ============================================================================== Author: Adam Haines ------------------------------------------------------------------------------ Date: 5/1/2009 ------------------------------------------------------------------------------ Inputs: @Check_Cnsts - This input parameter is a bit type. If the value is 0, constraints are not checked in the fk create scripts. If the value is 1, the db engine will check each fk before creating. The default value is 0. ----------------------------------------------------------------------------- Description: ----------------------------------------------------------------------------- This inline TVF select foreign keys that meet the parameters and creates drop and and create scripts for each foreign key. ------------------------------------------------------------------------------ Sample execution: SELECT * FROM [dbo].[fn_ScriptFKs](1) WHERE Ref_Table_Name = 'Contact' ============================================================================== */ CREATE FUNCTION dbo.fn_ScriptFKs ( --param to check the contraints RI before creating @Check_Cnsts BIT = 0 ) RETURNS TABLE RETURN( SELECT --The schema where the fk resides Schema_Name(t_par.Schema_Id) AS Par_Table_Schema, --The schema where the table resides t_par.name AS Par_Table_Name, --The schema for the table the fk references Schema_Name(t_ref.Schema_Id) AS Ref_Table_Schema, --The table the fk references t_ref.name AS Ref_Table_Name, --The index id the fk uses fk.[key_index_id] AS Index_Id, --Drop FK TSQL 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t_par.[schema_id])) + '.' + QUOTENAME(t_par.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.[name]) + ';' + CHAR(13) AS FK_Drop_Cmd, --Create FK TSQL 'ALTER TABLE ' + QUOTENAME(schema_name(t_par.[schema_id])) + '.' + QUOTENAME(t_par.[name]) + CASE WHEN @Check_Cnsts = 1 THEN ' WITH CHECK ' ELSE ' WITH NOCHECK ' END + 'ADD CONSTRAINT ' + QUOTENAME(fk.[name]) + ' FOREIGN KEY (' + COALESCE(STUFF( (SELECT ',' + QUOTENAME(COL_NAME(fkc.[parent_object_id],fkc.parent_column_id)) FROM sys.foreign_key_columns fkc WHERE fk.[object_id] = fkc.[constraint_object_id] ORDER BY fkc.[constraint_column_id] FOR xml PATH('')) ,1,1,''),'') COLLATE DATABASE_DEFAULT + ') REFERENCES ' + QUOTENAME(schema_name(t_ref.[schema_id])) + '.' + QUOTENAME(object_name(t_ref.[object_id])) + + ' (' + COALESCE(STUFF( (SELECT ',' + QUOTENAME(COL_NAME(fkc.[referenced_object_id],fkc.referenced_column_id)) FROM sys.foreign_key_columns fkc WHERE fk.[object_id] = fkc.[constraint_object_id] ORDER BY fkc.[constraint_column_id] FOR xml PATH('')) ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' + CASE WHEN fk.[update_referential_action] = 1 THEN ' ON UPDATE ' + fk.[update_referential_action_desc] ELSE '' END + CASE WHEN fk.[delete_referential_action] = 1 THEN ' ON DELETE ' + fk.[delete_referential_action_desc] ELSE '' END + CASE WHEN fk.[is_not_for_replication] = 1 THEN ' NOT FOR REPLICATION' ELSE '' END + ';' + CHAR(13) AS FK_Create_Cmd FROM sys.foreign_keys fk INNER JOIN sys.tables t_ref ON fk.[referenced_object_id] = t_ref.[object_id] INNER JOIN sys.tables t_par ON fk.[parent_object_id] = t_par.[object_id] WHERE t_ref.[is_ms_shipped] = 0 AND t_ref.[type] = 'U' AND t_par.[is_ms_shipped] = 0 AND t_par.[type] = 'U' AND fk.[is_ms_shipped] = 0 AND fk.[type] = 'F' ) GO
Sample Execution:
SELECT * FROM [dbo].[fn_ScriptFKs](1) WHERE Ref_Table_Name = 'Contact'
Results:
The last couple of posts I have made are gearing us toward a goal of creating a script to move indexes to another filegroup. By encapsulating logic we can make code easier to troubleshoot and manage.
Stay tuned….
2 comments:
that’s good, but you can try another way of repair Excel files
Great shaare
Post a Comment