SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 drop table foreign key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

desikankannan
Posting Yak Master

India
152 Posts

Posted - 11/12/2013 :  11:39:50  Show Profile  Send desikankannan a Yahoo! Message  Reply with Quote
Hi

i try to drop table which have foreign key relationship

DROP TABLE [dbo].[tblfuneral]

i want to check this table have foreign key exist




Desikankannan

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 11/12/2013 :  11:51:44  Show Profile  Reply with Quote
Alter the table to remove the foreign key relationship first.
To find the foreign key try:
SELECT f.name AS ForeignKey,
    SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
    SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
    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
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
GO



djj
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 11/12/2013 :  11:56:48  Show Profile  Reply with Quote
Run a query such as this. You can pickup additional columns from the sys.foreign_key_columns view if you need more info
SELECT  t.name AS TableName ,
        c.name AS ColumnName
FROM    sys.foreign_key_columns f
        INNER JOIN sys.tables t ON t.object_id = f.parent_object_id
        INNER JOIN sys.columns c ON f.parent_column_id = c.column_id
                                    AND c.object_id = f.parent_object_id
Go to Top of Page

desikankannan
Posting Yak Master

India
152 Posts

Posted - 11/12/2013 :  12:46:02  Show Profile  Send desikankannan a Yahoo! Message  Reply with Quote
Plese send me some sample example, i am not getting your point

quote:
Originally posted by James K

Run a query such as this. You can pickup additional columns from the sys.foreign_key_columns view if you need more info
SELECT  t.name AS TableName ,
        c.name AS ColumnName
FROM    sys.foreign_key_columns f
        INNER JOIN sys.tables t ON t.object_id = f.parent_object_id
        INNER JOIN sys.columns c ON f.parent_column_id = c.column_id
                                    AND c.object_id = f.parent_object_id




Desikankannan
Go to Top of Page

desikankannan
Posting Yak Master

India
152 Posts

Posted - 11/12/2013 :  12:48:39  Show Profile  Send desikankannan a Yahoo! Message  Reply with Quote

Here is my primary key table name tblfunerial and field name is fid

My foreign key table name deathdata and field name is fid

i want to check the realtionship table, the drop the table

quote:
Originally posted by djj55

Alter the table to remove the foreign key relationship first.
To find the foreign key try:
SELECT f.name AS ForeignKey,
    SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
    SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
    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
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
GO



djj



Desikankannan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/13/2013 :  00:23:45  Show Profile  Reply with Quote
Just add one moew condition to check for only those tables

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
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
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.parent_object_id) IN ('tblfunerial', 'deathdata')

Then you will get to know on which column you have keys....


--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 11/13/2013 :  01:09:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You can also try

EXEC sp_fkeys table_name

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 11/14/2013 :  08:15:24  Show Profile  Reply with Quote
sp_help [TableName] which will show all you want.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000