How To Drop All Foreign Keys in a MS SQL Server Database

Today I needed to remove all foreign keys in a database. This was necessary because I was building a script to build a database from an initial import, plus a bunch of new tables, and alter statements.
So I needed a clean slate before running the initial import (DROP + CREATE) script.

The script below completely blows away ALL foreign keys in your DB.

Enjoy!


SET NOCOUNT ON
GO

DECLARE Fkeys CURSOR FOR
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

OPEN Fkeys

DECLARE @Statement NVARCHAR(500)

FETCH NEXT FROM Fkeys INTO @statement
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'RUNNING ' + @statement
   EXEC sp_executesql @statement
   PRINT CHAR(13) + CHAR(13)
   FETCH NEXT FROM Fkeys INTO @statement
END

CLOSE Fkeys
DEALLOCATE Fkeys
GO
SET NOCOUNT OFF
GO

Tags

windows (10) microsoft (9) .net (6) google chrome (5) windows 7 (5) asp.net (4) google (4) google browser (4) development (3) javascript (3) mssql (3) rss (3) sql server (3) wordpress (3) automation (2) blogging (2) css (2) database (2) firefox (2) get all wordpress images (2) gmail (2) google docs (2) intel (2) internet explorer (2) linux (2) linux commands (2) microsoft word (2) mysql (2) netsh (2) network issue (2) proxy (2) seo tips (2) shell (2) sun (2) tfs (2) videos (2) wget (2) windows networking (2) windows vista (2) winhttp (2) .net 3.5 (1) .net 4.5 (1) .net async ctp3 (1) .net framework 4.0 (1) 404 (1) JungleDisk (1) access (1) active directory (1) addons (1) adobe acrobat (1) adobe dlm (1) adobe reader (1) adp (1) adsense (1) adtoll (1) adwords (1) amazon (1) antivirus (1) asp.net 4.0 (1) authentication (1) back links (1) backlinks (1) bacula (1) bash (1) batch files (1) blogger (1) box (1) browser exploits (1) category rss (1) cell phone (1) cell phone comparison (1) charting (1) cheap cell phones (1) cheap laptop upgrades (1) checkout (1) chrome (1) chrome.manifest (1) cloud (1) cloud hosting (1) cloud vps (1) code (1) color chart (1) colour chart (1) conditional formatting (1) config (1) configuration (1) context menu (1) copy (1) corrupt (1) credentials (1) cross-reference (1) database scripts (1) dba scripts (1) debian (1) decrypt (1) delete file windows vista (1) delete files (1) dell (1) dell laptop (1) dell studio (1) dell studio 1537 (1) dhcp (1) directory size (1) div (1) dns (1) document properties (1) dotnet (1) download (1) dreamhost (1) dreamhost coupon (1) dreamhost promo (1) dreamhost promo code (1) drive letter (1) drivers (1) duplicate content (1) editpad pro (1) encrypt (1) encryption (1) error (1) error code (1) excel (1) exception (1) external hard drive (1) facebook (1) faviconize (1) feeds (1) firefox 3 rc1 (1) firefox 3.1 (1) firefox addons (1) firefox tabs (1) firewall (1) firewall script (1) fix (1) fix .net framework (1) foreign keys (1) gmail 2.0 (1) gmail error (1) google chrome 2.0 (1) google chrome dev (1) google chrome exploit (1) google reader (1) google reader tags (1) gtdinbox (1) hard drive (1) hex color (1) hex colour (1) htaccess (1) html (1) html 5 (1) iis6 (1) installation (1) ipod touch (1) ipod touch 2g (1) ipod touch freeze (1) ipod touch magnet case (1) ipod touch magnet case problem (1) ipod touch problem (1) iterator pattern (1) itunes (1) java (1) joomla (1) jquery (1) laptop (1) laptop upgrade (1) laptops (1) latex (1) leeching (1) like button (1) link checker (1) linkbacks (1) linq (1) linqdatasource (1) lost password (1) making money online (1) map drive (1) mega cheap phones (1) microsoft excel (1) microsoft signature (1) microsoft store (1) microsoft web deploy (1) microsoft windows (1) microsoft word 2007 (1) minimize firefox tabs (1) mozy (1) ms word (1) msdeploy (1) msdtc (1) nant (1) netstumbler (1) network path not found (1) network path was not found (1) network problem (1) networking (1) new movies (1) nintendo (1) nirsoft (1) nocheckbrowser (1) number (1) odf (1) odt (1) online backups (1) open source browser (1) openoffice (1) oracle (1) oracle client (1) photoshop (1) phpmyadmin (1) podcast (1) powershell (1) pr checker (1) productivity (1) proxy server (1) proxycfg (1) putty (1) recover (1) registry (1) reinstall windows 7 (1) remote desktop (1) remove (1) repair (1) reset joomla admin password (1) rewrite (1) rsa (1) sandy bridge laptop (1) seagate momentus xt (1) seo tools (1) sequence (1) server monitoring (1) sftp (1) social networks (1) softlayer (1) soulseek (1) spreadsheet (1) spreadsheet formula (1) sql (1) sql scripts (1) sql server management studio (1) sqlclient (1) ssh (1) ssis (1) ssl (1) ssms (1) subst (1) tabmixplus (1) telstra (1) text editor (1) trust (1) unlock cell phone (1) unlock mobile phone (1) upgrade laptop hard drive (1) user management (1) vb.net (1) video download (1) virtual server (1) visual studio (1) vodafone (1) vodafone australia (1) vps (1) vps.net (1) wd external drive (1) web deploy (1) web dev (1) web development (1) web hosting (1) web security (1) webdev (1) webmail (1) webmaster tips (1) western digital (1) wifi networks (1) wii (1) win7 (1) windows 7 backup (1) windows 7 gadgets (1) windows 8 (1) windows 8 antivirus (1) windows error (1) windows live (1) windows live essentials (1) windows live toolbar (1) windows tips (1) windows web development (1) windows xp (1) winxp (1) wireless networks (1) word tips (1) wordpress 2.7 (1) wordpress plugin (1) wp super cache (1) yield (1) youtube download (1) youtube playlist download (1)