Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-23
14
Medium Priority
?
314 Views
Last Modified: 2012-05-12
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
Comment
Question by:rayluvs
14 Comments
 
LVL 5

Accepted Solution

by:
Eduardo Goicovich earned 1424 total points
ID: 36586674
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
 
LVL 9

Assisted Solution

by:dexterrajesh
dexterrajesh earned 288 total points
ID: 36586675
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
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 288 total points
ID: 36587006
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:rayluvs
ID: 36589158
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
 

Author Comment

by:rayluvs
ID: 36593319
We run the script in our SQL 2000 and it give error.  It seems that wring syntax for SQL 2000.

Please advice
0
 

Author Comment

by:rayluvs
ID: 36718475
Whats the syntax for SQL 2000
0
 
LVL 5

Assisted Solution

by:Eduardo Goicovich
Eduardo Goicovich earned 1424 total points
ID: 36896237
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
 

Author Comment

by:rayluvs
ID: 36896553
Great thanx!... how about disabling / enabling them, can it be done and does it have any negative effect is we disable then enable?
0
 
LVL 5

Assisted Solution

by:Eduardo Goicovich
Eduardo Goicovich earned 1424 total points
ID: 36897054
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
 
LVL 5

Assisted Solution

by:Eduardo Goicovich
Eduardo Goicovich earned 1424 total points
ID: 36897055
enable again

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

0
 

Author Comment

by:rayluvs
ID: 36897081
Thanx!  any side effect or bad effect when doing these enable/disable?
0
 
LVL 5

Assisted Solution

by:Eduardo Goicovich
Eduardo Goicovich earned 1424 total points
ID: 36897099
no...only be sure to bring contraints back when you are done
0
 

Author Comment

by:rayluvs
ID: 36897126
Thanx!
0
 

Author Closing Comment

by:rayluvs
ID: 36897254
Thanx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question