Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
352 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

3873 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
2241 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
22864 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  
 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.18 seconds. Powered By: Snitz Forums 2000