Head.SmackOnTable();

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

via SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database « Journey to SQL Authority with Pinal Dave.

This script is a godsend. Basically, we had a botched install of Yet Another Forum, and we had to delete it (YAY!), but because of the FK constraints, we couldnt do a simple “DROP TABLE *” on the database, and nor could we just drop the database.

So I came up with this script that generated the SQL Script for me, based on the above:

SELECT 'ALTER TABLE ' +  OBJECT_NAME(f.parent_object_id) + ' DROP CONSTRAINT [' + f.name + ']' AS Moo, f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
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

Can be refined alot better, but works. The resulting script is as follows – note I added the “drop table” by hand:

ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Forum]
ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Forum]
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_Forum]
ALTER TABLE yaf_WatchForum DROP CONSTRAINT [FK_yaf_WatchForum_yaf_Forum]
ALTER TABLE yaf_NntpForum DROP CONSTRAINT [FK_yaf_NntpForum_yaf_Forum]
ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_Forum]
ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Forum]
ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Message]
ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Message]
ALTER TABLE yaf_Attachment DROP CONSTRAINT [FK_yaf_Attachment_yaf_Message]
ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_Message]
ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Poll]
ALTER TABLE yaf_Choice DROP CONSTRAINT [FK_yaf_Choice_yaf_Poll]
ALTER TABLE yaf_PollVote DROP CONSTRAINT [FK_yaf_PollVote_yaf_Poll]
ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Topic]
ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_Topic]
ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_Topic]
ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Topic]
ALTER TABLE yaf_WatchTopic DROP CONSTRAINT [FK_yaf_WatchTopic_yaf_Topic]
ALTER TABLE yaf_NntpTopic DROP CONSTRAINT [FK_yaf_NntpTopic_yaf_Topic]
ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_User]
ALTER TABLE yaf_Message DROP CONSTRAINT [FK_yaf_Message_yaf_User]
ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_User]
ALTER TABLE yaf_Topic DROP CONSTRAINT [FK_yaf_Topic_yaf_User2]
ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_User]
ALTER TABLE yaf_CheckEmail DROP CONSTRAINT [FK_yaf_CheckEmail_yaf_User]
ALTER TABLE yaf_PMessage DROP CONSTRAINT [FK_yaf_PMessage_yaf_User1]
ALTER TABLE yaf_WatchForum DROP CONSTRAINT [FK_yaf_WatchForum_yaf_User]
ALTER TABLE yaf_WatchTopic DROP CONSTRAINT [FK_yaf_WatchTopic_yaf_User]
ALTER TABLE yaf_UserGroup DROP CONSTRAINT [FK_yaf_UserGroup_yaf_User]
ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_User]
ALTER TABLE yaf_UserPMessage DROP CONSTRAINT [FK_yaf_UserPMessage_yaf_User]
ALTER TABLE yaf_EventLog DROP CONSTRAINT [FK_yaf_EventLog_yaf_User]
ALTER TABLE yaf_User DROP CONSTRAINT [FK_yaf_User_yaf_Rank]
ALTER TABLE yaf_Category DROP CONSTRAINT [FK_yaf_Category_yaf_Board]
ALTER TABLE yaf_Rank DROP CONSTRAINT [FK_yaf_Rank_yaf_Board]
ALTER TABLE yaf_AccessMask DROP CONSTRAINT [FK_yaf_AccessMask_yaf_Board]
ALTER TABLE yaf_Active DROP CONSTRAINT [FK_yaf_Active_yaf_Board]
ALTER TABLE yaf_User DROP CONSTRAINT [FK_yaf_User_yaf_Board]
ALTER TABLE yaf_BannedIP DROP CONSTRAINT [FK_yaf_BannedIP_yaf_Board]
ALTER TABLE yaf_Group DROP CONSTRAINT [FK_yaf_Group_yaf_Board]
ALTER TABLE yaf_NntpServer DROP CONSTRAINT [FK_yaf_NntpServer_yaf_Board]
ALTER TABLE yaf_Smiley DROP CONSTRAINT [FK_yaf_Smiley_yaf_Board]
ALTER TABLE yaf_Registry DROP CONSTRAINT [FK_yaf_Registry_yaf_Board]
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_Group]
ALTER TABLE yaf_UserGroup DROP CONSTRAINT [FK_yaf_UserGroup_yaf_Group]
ALTER TABLE yaf_UserPMessage DROP CONSTRAINT [FK_yaf_UserPMessage_yaf_PMessage]
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT [FK_yaf_ForumAccess_yaf_AccessMask]
ALTER TABLE yaf_UserForum DROP CONSTRAINT [FK_yaf_UserForum_yaf_AccessMask]
ALTER TABLE yaf_NntpForum DROP CONSTRAINT [FK_yaf_NntpForum_yaf_NntpServer]
ALTER TABLE yaf_NntpTopic DROP CONSTRAINT [FK_yaf_NntpTopic_yaf_NntpForum]
ALTER TABLE yaf_Forum DROP CONSTRAINT [FK_yaf_Forum_yaf_Category]

drop table dbo.yaf_AccessMask
drop table dbo.yaf_Active
drop table dbo.yaf_Attachment
drop table dbo.yaf_BannedIP
drop table dbo.yaf_Board
drop table dbo.yaf_Category
drop table dbo.yaf_CheckEmail
drop table dbo.yaf_Choice
drop table dbo.yaf_EventLog
drop table dbo.yaf_Forum
drop table dbo.yaf_ForumAccess
drop table dbo.yaf_Group
drop table dbo.yaf_Mail
drop table dbo.yaf_Message
drop table dbo.yaf_NntpForum
drop table dbo.yaf_NntpServer
drop table dbo.yaf_NntpTopic
drop table dbo.yaf_PMessage
drop table dbo.yaf_Poll
drop table dbo.yaf_PollVote
drop table dbo.yaf_Rank
drop table dbo.yaf_Registry
drop table dbo.yaf_Replace_Words
drop table dbo.yaf_Smiley
drop table dbo.yaf_Topic
drop table dbo.yaf_User
drop table dbo.yaf_UserForum
drop table dbo.yaf_UserGroup
drop table dbo.yaf_UserPMessage
drop table dbo.yaf_WatchForum
drop table dbo.yaf_WatchTopic
drop view dbo.yaf_vaccess