Generate FKs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Generate FKs

Author

Topic

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-11-20 : 02:38:14

One of my first tasks on my current project was to generate all of the SQL scripts needed to rebuild the database. Of course, one of the catches was that everything needed to be separated out (table columns in different script from PKs different from FKs different from DEFAULTs, etc., etc.). And we wanted our own comment block inserted. So, I couldn't just use the Generate SQL Script from Enterprise Manager and be done with it. I was going to need to do some more work. Well, here's one of the scripts I wrote to build the FK scripts. Execute this script, then copy & paste the results into a new window. Break apart into separate .sql files as desired.

NOTE: Be sure to show results in Text (not grid) and change the display options in QA to return more than just 256 characters.


/***************************************************************************************************
  Author:  Mark Caldwell 
  Date:    11/15/2002
  Descrip: Generate scipt commands to ADD FOREIGN KEY CONSTRAINTS for constraints already in DB.

           NOTE:  Be sure to set your Tools/Options/Results/Maximum Characters per column to a
                  large enough number (such as 2000) to display the entire command.
***************************************************************************************************/

SELECT 
'----------------------------------------------------------------------------------------------------
--  ' + so2.name + ' to ' + so3.name + '
----------------------------------------------------------------------------------------------------
IF OBJECTPROPERTY(OBJECT_ID(N''' + so1.name + '''), ''IsForeignKey'') = 1 BEGIN
  ALTER TABLE ' + so2.name + ' DROP CONSTRAINT ' + so1.name + '
  PRINT ''    -- DRP - ' + so1.name + '''
END
G' + 'O

ALTER TABLE ' + so2.name + '
  ADD CONSTRAINT ' + so1.name + ' 
  FOREIGN KEY (' + ISC1.COLUMN_NAME + ')
  REFERENCES ' + so3.name + '(' + ISC2.COLUMN_NAME + ')

PRINT ''    -- ADD - ' + so1.name + '''
G' + 'O

'
FROM sysforeignkeys sfk
JOIN sysobjects so1 on sfk.constid = so1.id
JOIN sysobjects so2 on sfk.fkeyid = so2.id
JOIN sysobjects so3 on sfk.rkeyid = so3.id
JOIN INFORMATION_SCHEMA.COLUMNS ISC1 on so2.name = ISC1.TABLE_NAME AND sfk.fkey = ISC1.ORDINAL_POSITION
JOIN INFORMATION_SCHEMA.COLUMNS ISC2 on so3.name = ISC2.TABLE_NAME AND sfk.rkey = ISC2.ORDINAL_POSITION
ORDER BY so2.name

Edited by - AjarnMark on 11/20/2002 02:46:11

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-03-25 : 10:37:12

Thank you.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources