Solved

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

Posted on 2011-09-23
14
278 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

856 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