• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

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!

0
rayluvs
Asked:
rayluvs
7 Solutions
 
Eduardo GoicovichIT 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
 
dexterrajeshCommented:
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
 
ThomasianCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 GoicovichIT 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 GoicovichIT 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 GoicovichIT 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 GoicovichIT 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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now