Solved

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

Posted on 2011-09-23
14
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 5

Accepted Solution

by:
Eduardo Goicovich earned 356 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 72 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 72 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 356 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 356 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 356 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 356 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
Create a Calendar table 29 45
Do not display comma when no last name 8 48
invoke-sqlcmd help 5 34
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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