February 24, 2011 at 11:09 pm
I have a denormalized table that provides code values for various lookups. I am doing a proof of concept to breakout each of the sets of values into their own tables, and I need to delete the data from the old table to prove that it is working right. I've used a query from Dave Pinal's blog to identify the foreign key constraints that need to be dropped before I can delete the data, but it's not working correctly because when I try to truncate the table SQL keeps telling that there are still foreign key constraints on the table. Does anyone have a better method of finding all foreign key constraints on a table?
Thanks for the help!;-)
From Dave Pinal's blog:
SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
February 25, 2011 at 8:04 am
sp_help wlll tell you all referenced as well as referencing keys. You want to look at the last two result sets:
USE AdventureWorks
GO
EXEC sys.sp_help @objname = 'HumanResources.EmployeeDepartmentHistory'
If you are interested you can see what sp_help is doing to find this information by using sp_helptext:
EXEC sys.sp_helptext @objname = 'sys.sp_help'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 26, 2011 at 8:06 am
Or simply press XDetails button (free plugin) on that table name in sql editor,
and you'll see all info you need.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy