SQL Script for identify if a table has foreign keys and how to disable-enable them

We have to do change all our tables Master ID codes for security purpose.  We have  a lot of tables and just finished all the scripts necessary for the change.

We were told that we have to first disable any foreign keys between the tables, then do the actual ID change and finally enable the foreign keys again.

  1. Since we have a lot of tables, is there a script we can use to know what tables
      have foreign keys?
  2. Is there a way via script to disable/enable foreign keys?
  3. Finally, based on EE experiences on this type of task at hand, any other consideration
      prior performing this task?

Thank you very much!

rayluvsAsked:
Who is Participating?
 
Eduardo GoicovichConnect With a Mentor IT ConsultantCommented:
1. for foreign keys

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

reference http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-with-foreign-key-constraint-in-database/

2. foreign keys status
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, ‘CNSTISDISABLED’) = 0 THEN ‘ENABLED’
ELSE ‘DISABLED’
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

http://decipherinfosys.wordpress.com/2008/02/20/disableenable-foreign-key-and-check-constraints-in-sql-server/

3. for disable them

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

4. for enable them again

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
0
 
dexterrajeshConnect With a Mentor Commented:
hi,

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Open in new window


reference: http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
http://sqlserver2000.databases.aspfaq.com/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html

hope this helps

Considerations:

Ensure your front end code works fine with the updated values(constants\hard coded if any)
0
 
ThomasianConnect With a Mentor Commented:
You can set the foreign key to ON UPDATE CASCADE. With this, you just need to update the ID from the Master table and all the referenced columns will also be updated automatically.

http://www.techrepublic.com/blog/datacenter/defining-cascading-referential-integrity-constraints-in-sql-server/128
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rayluvsAuthor Commented:
Thank you very much!   We just saw the priblem, the SQL is 2000, where does the script change so we can run this in SQL 2000?
0
 
rayluvsAuthor Commented:
We run the script in our SQL 2000 and it give error.  It seems that wring syntax for SQL 2000.

Please advice
0
 
rayluvsAuthor Commented:
Whats the syntax for SQL 2000
0
 
Eduardo GoicovichConnect With a Mentor IT ConsultantCommented:
this one is for s2k (http://bytestopshere.wordpress.com/2008/11/01/sql-server-script-to-find-foreign-key-dependencies/)

select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    ,  cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  'F'
 ORDER BY cast(p.name as varchar(255))
0
 
rayluvsAuthor Commented:
Great thanx!... how about disabling / enabling them, can it be done and does it have any negative effect is we disable then enable?
0
 
Eduardo GoicovichConnect With a Mentor IT ConsultantCommented:
3. for disable them

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

4. for enable them again

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all

only if modifying data, you could break referential data integrity
0
 
Eduardo GoicovichConnect With a Mentor IT ConsultantCommented:
enable again

EXEC sp_msforeachtable "ALTER TABLE ?  WITH CHECK CHECK CONSTRAINT all"

0
 
rayluvsAuthor Commented:
Thanx!  any side effect or bad effect when doing these enable/disable?
0
 
Eduardo GoicovichConnect With a Mentor IT ConsultantCommented:
no...only be sure to bring contraints back when you are done
0
 
rayluvsAuthor Commented:
Thanx!
0
 
rayluvsAuthor Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.