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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply