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) + ']'
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.
'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'