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'

2 comments:

Note: Only a member of this blog may post a comment.