[MSSQL] czyszczenie tabel

Ostatni mój temat tutaj przeszedł bez echa, mam nadzieję, że teraz będzie lepiej ;)

Ogólnie chodzi o to, żeby usunąć dane z tabel w bazie. Jest ich prawie 500, ale trzeba wyczyścić tylko te niesłownikowe, czyli nie mające "DICT" w nazwie. Delete jest wolne i zapycha log, więc nie może być użyte, trzeba zrobić truncate. Z nim z kolei jest ten problem, że wywala się na kluczach obcych.

Googlowałem i na podstawie tego, co znalazłem, skleiłem takie coś:

-- wyłączenie FK
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

-- kursor do czyszczenia tabel
DECLARE queriesCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT    
    'TRUNCATE TABLE '
    + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '; '
FROM    INFORMATION_SCHEMA.TABLES
WHERE    TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME NOT LIKE '%DICT%'

-- zapytanie
DECLARE @query varchar(4000)

OPEN queriesCursor
-- pętla
FETCH NEXT FROM queriesCursor INTO @query
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@query)
    FETCH NEXT FROM queriesCursor INTO @query
END
-- zamknięcie kursora
CLOSE queriesCursor
DEALLOCATE queriesCursor

-- włączenie FK
exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Jak widać przed wykonaniem pętli czyszczącej próbuję wyłączyć constrainty, ale to nie chce działać, bo nadal wyskakuje błąd naruszenia kluczy.

Ma ktoś jakiś pomysł?
Z góry dzięki.

Nie ma możliwości użycia truncate na tabali, do której są odwołania poprzez klucze obce. Zastosowany przez Ciebie mechanizm pozwala jedynie na wstawienie rekordów do tabeli podrzędnej bez sprawdzenia klucza głównego w tabeli nadrzędnej ale nie pozwoli to na użycie truncate.

Fragment z MSDN:

Restrictions

You cannot use TRUNCATE TABLE on tables that:
Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
Participate in an indexed view.
Are published by using transactional replication or merge replication.

For tables with one or more of these characteristics, use the DELETE statement instead.

Rozwiązaniem może być usunięcie kluczy obcych, wykonanie truncate i założenie kluczy ponownie.

Pytam, jak to zrobić. Jest na to jakaś prosta metoda typu odgórne wyłączenie wszystkich constraintów dla wszystkich tabel, czy mam osobno oskryptować każdą tabelę, co jest bardzo pracochłonne?

@somekind - wystarczy w kursorze zbudować sobie skrypt do dropowania wszystkich constraintów (ALTER TABLE dbo.x DROP CONSTRAINT ...), a następnie napisać skrypt do tworzenia ich na nowo (ALTER TABLE dbo.x ADD CONSTRAINT ...). Constrainty masz w tabeli sys.objects. Wystarczy je odfiltrować po typie (PK - primary key, FK, - foreign key) oraz sprawdzić po parent_object_id = object_id na której tabeli są założone.

Przygotowane w ten sposób skrypty wykonujesz przed TRUNCATE i po nim.

Jak poczekasz kilkanaście minut to naskrobie skrypt tylko muszę się przelogować na win.

/edit: obyło się bez kursora, byłby potrzebny tylko wtedy gdybyśmy chcieli każdy constraint na bieżąco dropować. Wykonaj oba zapytania i zapisz oba powstałe skrypty! Następnie wykonaj dropujący przed TRUNCATE i odtwarzający po nim. Aha i przetestuj je na jednej tabeli wcześniej, bo ja ich nie testowałem.

Skrypt dropujący FK:

SELECT 'ALTER TABLE ' + ftab.name + ' DROP CONSTRAINT ' + fk.name 
FROM dbo.sys_sysreferences r WITH (NOLOCK)
INNER JOIN dbo.sys_sysindexes    i WITH (NOLOCK) ON r.rkeyid = i.id AND r.rkeyindid = i.indid
INNER JOIN dbo.sys_sysobjects   fk WITH (NOLOCK) ON fk.id = r.constid
INNER JOIN dbo.sys_sysobjects ftab WITH (NOLOCK) ON ftab.id = r.fkeyid
INNER JOIN dbo.sys_sysobjects rtab WITH (NOLOCK) ON rtab.id = r.rkeyid;

Skrypt odtwarzający FK:

SELECT 'ALTER TABLE '+ftab.name + ' ADD CONSTRAINT '+QUOTENAME(fk.name)+' FOREIGN KEY ('+REPLACE(
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey1)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey2)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey3)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey4)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey5)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey6)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey7)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey8)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey9)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey10)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey11)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey12)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey13)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey14)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey15)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.fkeyid,r.fkey16)),'')+',',
  REPLICATE(',',17-r.keycnt),
  '')+') REFERENCES '+QUOTENAME(rtab.name)+' ('+  REPLACE(
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey1)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey2)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey3)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey4)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey5)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey6)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey7)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey8)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey9)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey10)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey11)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey12)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey13)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey14)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey15)),'')+','+
  ISNULL(QUOTENAME(COL_NAME(r.rkeyid,r.rkey16)),'')+',',
  REPLICATE(',',17-r.keycnt),
  '')+')'+
 CASE WHEN CASE OBJECTPROPERTY(r.constid,'CnstIsUpdateCascade') WHEN 1 THEN 'CASCADE' ELSE 'NO ACTION' END ='CASCADE' THEN ' ON UPDATE CASCADE' ELSE '' END+
 CASE WHEN CASE OBJECTPROPERTY(r.constid,'CnstIsDeleteCascade') WHEN 1 THEN 'CASCADE' ELSE 'NO ACTION' END ='CASCADE' THEN ' ON DELETE CASCADE' ELSE '' END
FROM dbo.sys_sysreferences r WITH (NOLOCK)
INNER JOIN dbo.sys_sysindexes    i WITH (NOLOCK) ON r.rkeyid = i.id AND r.rkeyindid = i.indid
INNER JOIN dbo.sys_sysobjects   fk WITH (NOLOCK) ON fk.id = r.constid
INNER JOIN dbo.sys_sysobjects ftab WITH (NOLOCK) ON ftab.id = r.fkeyid
INNER JOIN dbo.sys_sysobjects rtab WITH (NOLOCK) ON rtab.id = r.rkeyid;

Wielkie dzięki :)

Dodałem obsługę schemas i zamieniłem "dbo.sys_" na "sys.", żeby ruszyło.

Problem w tym, że okazuje się, że to nie wszystko - teraz wywala się na wartościach domyślnych, bo to też są constrainty :/
Da się jakoś łatwo zmodyfikować ten Twój skrypt? Bo nigdzie nie widzę sprawdzania typu obiektu...

skrypt dropujący D:

SELECT 'ALTER TABLE ' + QUOTENAME(o.name) + ' DROP CONSTRAINT ' + QUOTENAME(cnst.name)
FROM dbo.sys_sysobjects o WITH (NOLOCK)
INNER JOIN dbo.sys_syscolumns c WITH (NOLOCK) ON o.id = c.id
INNER JOIN dbo.sys_sysobjects cnst WITH (NOLOCK, FASTFIRSTROW) ON cnst.id = c.cdefault
WHERE o.xtype = 'U';

skrypt odtwarzający D:

SELECT 'ALTER TABLE ' + QUOTENAME(o.name) + ' ADD CONSTRAINT ' + QUOTENAME(cnst.name)
    + ' DEFAULT ' + CONVERT(nvarchar(4000) ,OBJECT_DEFINITION(c.cdefault)) + ' FOR ' + QUOTENAME(c.name)
FROM dbo.sys_sysobjects o WITH (NOLOCK)
INNER JOIN dbo.sys_syscolumns c WITH (NOLOCK) ON o.id = c.id
INNER JOIN dbo.sys_sysobjects cnst WITH (NOLOCK, FASTFIRSTROW) ON cnst.id = c.cdefault
WHERE o.xtype = 'U';

Wielkie dzięki :)

Przerobiłem to nieco, żeby nie trzeba było ręcznie generować skryptów pośrednich i można było zrobić wszystko na raz. Razem z usunięciem i przywróceniem widoków czyszczenie bazy trwa ok. 15 sekund, a więc rezultat jest zadowalający.
Przy okazji okazało się, że jedna tabela słownikowa była powiązana przez FK ze zwykłą tabelą odwrotnie :D

W ogóle, to jakbym mógł, to bym Ci dał Nobla za ten kod ;)
Swoją drogą, to dziwne, że tak banalna i zdawałoby się powszechna operacja, jest tak skomplikowana do wykonania i nigdzie w necie nie ma rozwiązania tego problemu...

a tak swoja droga to probowaliscie kiedys z usuwaniem kaskadowym :)

Zastanawia mnie czy po wylaczeniu constraintow (nocheck contraint all) zwykly delete nie da rady (bo wiem ze z truncate sa problemy)

Delete da radę bez problemu, tylko trwa wieki i niekoniecznie wykona się do końca, jeśli logi transakcji mają ograniczony rozmiar.