Tuesday, October 22, 2013

Delete all the foreign key relation in your SQL Server database


DECLARE @cmd nvarchar(1000)
DECLARE @fk_table_name nvarchar(1000)
DECLARE @fk_name nvarchar(1000)

DECLARE cursor_fkeys CURSOR FOR
   SELECT  OBJECT_NAME(fk.parent_object_id) AS fk_table_name,
                  fk.name as fk_name
   FROM    sys.foreign_keys fk  JOIN 
           sys.tables tbl ON tbl.OBJECT_ID = fk.referenced_object_id
   -- WHERE OBJECT_NAME(fk.parent_object_id) in ('table1', 'table2', 'table2')

OPEN cursor_fkeys
FETCH NEXT FROM cursor_fkeys
      INTO @fk_table_name, @fk_name

WHILE @@FETCH_STATUS=0
BEGIN
      -- build alter table statement
      SET @cmd = 'ALTER TABLE [' + @fk_table_name + '] DROP CONSTRAINT [' + @fk_name + ']'
      -- execute it
      exec dbo.sp_executesql @cmd

      FETCH NEXT FROM cursor_fkeys
      INTO @fk_table_name, @fk_name
END
CLOSE cursor_fkeys

DEALLOCATE cursor_fkeys

SQL Optimization

  SQL Optimization  1. Add where on your query  2. If you remove some data after the data return then remove the remove condition in the sel...