sentient beings

If you've ever found the need to drop or remove all foreign keys on a database then here's a little script that runs without cursors.

 
DECLARE @SQL NVARCHAR(MAX)	
 
SELECT
	@SQL = '-- Start '
 
WHILE @SQL > ''
BEGIN
	SELECT
		@SQL = ''
 
	SELECT
		@SQL = @SQL +
			CASE
				WHEN DATALENGTH(@SQL) < 7500 THEN
					N'alter table ' + QUOTENAME(schema_name(schema_id))
					+ N'.'
					+ QUOTENAME(OBJECT_NAME(parent_object_id))
					+ N' drop constraint '
					+ name
					+ CHAR(13) + CHAR(10) --+ 'GO' + CHAR(13) + CHAR(10)
				ELSE
					''
			END
	FROM
		sys.foreign_keys
 
	PRINT @SQL
	EXEC SP_EXECUTESQL @SQL
	PRINT '---8<------------------------------------------------------------------------------------------'
END
 
GO
 
---8<-------------------------------------------------------------
-- Uncomment the line below to drop all tables too
 
--exec sp_msforeachtable 'drop table ?'
 
PRINT 'All done'