Let me start by saying that before you do something like this you should definitely determine why you think it’s a good idea. Foreign keys can slow INSERT operations for example, but the odds are they were created for a reason. Still, if you’ve evaluated all the possibilities and this is something you feel is appropriate, you can use a formatted SELECT statement to generate a script, then execute the script.

Formatted SELECT statements can be a DBAs best friend. Put together with the SQLCMD utility, they can be leveraged to create some very robust maintenance operations. In your case, running a formatted SELECT like the following, output to a file, then using SQLCMD to run the file could make for an automated “disable foreign key checks” routine.


SELECT 'ALTER TABLE ' + QUOTENAME(LTRIM(RTRIM([TABLE_SCHEMA]))) + ‘.’ + QUOTENAME(LTRIM(RTRIM([TABLE_NAME]))) + ‘ NOCHECK CONSTRAINT ALL’ FROM [INFORMATION_SCHEMA].[TABLES] WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(LTRIM(RTRIM([TABLE_SCHEMA]))) + ‘.’ + QUOTENAME(LTRIM(RTRIM([TABLE_NAME])))), ‘IsMSShipped’) = 0 AND [TABLE_TYPE] = ‘BASE TABLE’