Cacher is the code snippet organizer for pro developers

--------------------------------------------------------------------------------------------------------------------
--------------------- This script will drop all foreign keys and then tables
--------------------------------------------------------------------------------------------------------------------
DECLARE @SelectDelete nvarchar(10) SET @SelectDelete = 'SELECT' --SELECT/DELETE - Set this variable to SELECT To show what will be deleted(will not delete anything), using DELETE will delete the items
DECLARE @TableName nvarchar(255) SET @TableName = 'cms%' --This supports %
DECLARE @SchemaName nvarchar(255) SET @SchemaName = 'dbo' --This does not support %


--------------------------------------------------------------------------------------------------------------------------


IF(@SelectDelete <> 'SELECT' AND @SelectDelete <> 'DELETE') --Chech input values
begin
	RAISERROR('--------------------@SelectDelete not valid----------------------------',20,-1) WITH LOG
end
IF OBJECT_ID('tempdb..#FKItems') IS NOT NULL DROP TABLE #FKItems
IF OBJECT_ID('tempdb..#DROPTables') IS NOT NULL DROP TABLE #DROPTables



--------------------------FOREIGN KEY PART------------------------------------

--Find all foreign Keys which should be dropped
--Add a row number for the loop
select ROW_NUMBER() OVER (ORDER BY t.name) as Id, t.name as TableName, o.name as FKName,  'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + o.name as DropSql
INTO #FKItems
FROM sys.objects o
JOIN sys.tables t on o.parent_object_id = t.object_id
JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE o.type = 'F' AND t.name LIKE @TableName and s.name = @SchemaName


if (@SelectDelete = 'DELETE') --if we are deletin, else just select the values
BEGIN
--We start with the lowest rowsnumber/id
	Declare @Id int SELECT  @Id = min(Id) from #FKItems
	Declare @Sql nvarchar(2000)
	
	while @Id is not null
	begin
	    SELECT @Sql = 'ALTER TABLE ' + fki.TableName + ' DROP CONSTRAINT ' + fki.FKName FROM #FKItems fki WHERE Id = @Id
		--SELECT @Sql
		EXEC (@Sql)
		--Find det næstmindste id fra det vi havde før
	   select @Id = min(Id) from #FKItems where Id > @Id
	END
END
ELSE
BEGIN
	SELECT DropSql as ForeignKeysToDrop FROM #FKItems
END 

----------------------------TABLE PART-----------------------------------------------

--Select all tables that should be deleted
SELECT 'DROP TABLE dbo.' + TABLE_NAME + '' SqlQuery
into  #DROPTables
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE @TableName AND TABLE_SCHEMA = @SchemaName


--Select the
DECLARE @SQLString nvarchar(2000)
select @SQLString=coalesce(@SQLString + ' ',' ') + SqlQuery from #DROPTables

IF (@SelectDelete = 'DELETE')
BEGIN
	EXEC (@SQLString)
END
ELSE
BEGIN
	SELECT SqlQuery as TablesToDrop FROM #DROPTables
END