May 11, 2011 at 2:56 am
Hi, I have a table with a column ItemCode, what I want is to keep only those ItemCode which have been defined in the documents where the rest should be removed.
My concern is I not sure whether other tables are referring to the rest of the ItemCode. Is it a way to find out this 'foreign key constraint' ? Thanks
May 11, 2011 at 10:07 am
Do you have table definitions, to include FK Constraints & sample data?
Could you restate the question for it could easily be misinterpreted?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 11:38 am
If you are trying to find which Foreign Keys reference a particular column, this link might get you going:
May 11, 2011 at 11:45 am
There are a number of ways that you can approach this. I tend to use the system views, because they have some of the necessary joins already set up and the information is more user friendly. Here is the a query that list child table, constraint, and parent table using the system views "Information_Schema.Table_Constraints" and "Information_Schema.Referential_Constraints".
SELECT ct.Table_Name AS Child_Table, rc.Constraint_Name AS FK_Constraint_Name, pt.Table_Name AS Parent_Table
FROM Information_Schema.Referential_Constraints AS rc
INNER JOIN Information_Schema.Table_Constraints AS pt
ON rc.Unique_Constraint_Name = pt.Constraint_Name
INNER JOIN Information_Schema.Table_Constraints AS ct
ON rc.Constraint_Name = ct.Constraint_Name
It would be easy to add a where clause to this to filter for a particular parent or child table. If you need to know the specific columns, there is also a system view called "Information_Schema.Constraint_Column_Usage".
Since the Information_Schema views are relatively new, many people who have worked with SQL Server for awhile tend to use the sys.objects view.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 11, 2011 at 11:53 am
It's not clear if you are looking for information on the constaint, or trying to find which data is linked to, or not linked to, FK values or PK values.
If you can show in an example what you are trying to do, we can help. As it is now, it is not clear what your question is.
May 11, 2011 at 1:30 pm
Are you trying to find which data exists in one table but not another?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply