drop table foreign key - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

drop table foreign key

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2013-11-12 : 11:39:50

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

352 Posts

Posted - 2013-11-12 : 11:51:44

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-12 : 11:56:48

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
Posting Yak Master

152 Posts

Posted - 2013-11-12 : 12:46:02

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

desikankannan
Posting Yak Master

152 Posts

Posted - 2013-11-12 : 12:48:39

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-13 : 00:23:45

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-13 : 01:09:30

You can also try

EXEC sp_fkeys table_name

Madhivanan

Failing to plan is Planning to fail

keyursoni85
Posting Yak Master

233 Posts

Posted - 2013-11-14 : 08:15:24

sp_help [TableName] which will show all you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources