Sunday, December 27, 2009

Quick way to query /script foreign key constraints

I needed to constantly query foreign key constraints as I was adding many new ones to a database for testing and removing:


select name, object_name(parent_obj) from sysobjects where xtype='f'
or if you want to use sys.objects instead:

select name, OBJECT_NAME(parent_object_id) from sys.objects where type='f'


F = foreign key constraint


using this you can create a script to drop constraints:


SELECT 'ALTER TABLE [dbo].[' +OBJECT_NAME(parent_object_id) +'] DROP CONSTRAINT [' + OBJECT_NAME(OBJECT_ID) + ']'
FROM sys.objects
WHERE type='f'

or I can add them. In this case I want to add a field called CompanyId to every table, and add a constraint to every table to reference this field.


SELECT
'ALTER TABLE [dbo].[' +name +'] add [CompanyId] [int] NOT NULL CONSTRAINT [DF_' + name + '_CompanyId] DEFAULT ((1))',
'ALTER TABLE [dbo].[' +name +'] WITH CHECK ADD CONSTRAINT [FK_' + name + '_Company] FOREIGN KEY([CompanyId]) REFERENCES [dbo].[Company] ([CompanyId])'
FROM sysobjects where type='u'

4 comments:

  1. Never put off till tomorrow what may be done today..........................

    ReplyDelete
  2. 如果成為一支火柴,也要點亮一個短暫的宇宙;如果是一隻烏鴉,也要叫疼閉塞的耳膜。 ....................................................

    ReplyDelete
  3. 任何你憂慮的事,你都應該去採取一點行動,不要只是在那邊想..................................................

    ReplyDelete
  4. Seems chinese proverbs are a hit on this blog.

    ReplyDelete