Come si tronchiano tutte le tabelle in un database usando TSQL?

Ho un ambiente di test per un database che voglio ricaricare con nuovi dati all’inizio di un ciclo di test. Non sono interessato a ribuild l’intero database, semplicemente semplicemente “reimpostando” i dati.

Qual è il modo migliore per rimuovere tutti i dati da tutte le tabelle usando TSQL? Sono disponibili stored procedure, viste, ecc.? Non voglio creare manualmente e mantenere istruzioni troncate per ogni tabella, preferirei che fosse dinamico.

Per SQL 2005,

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 

Coppia più collegamenti per il 2000 e il 2005/2008 ..

Quando si tratta dell’eliminazione di dati da tabelle che hanno relazioni con le chiavi esterne – che è fondamentalmente il caso con qualsiasi database correttamente progettato – possiamo disabilitare tutti i vincoli, eliminare tutti i dati e quindi riabilitare i vincoli

 -- disable all constraints EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in all tables EXEC sp_MSForEachTable "DELETE FROM ?" -- enable all constraints exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 

Altro su disabilitare i vincoli e i trigger qui

se alcune delle tabelle hanno colonne di identity framework, potremmo voler ridistribuirle

 EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)" 

Si noti che il comportamento di RESEED varia tra la nuovissima tabella e quella che ha avuto alcuni dati inseriti precedentemente da BOL :

DBCC CHECKIDENT (‘table_name’, RESEED, newReseedValue)

Il valore dell’id quadro corrente è impostato su newReseedValue. Se nessuna tabella è stata inserita nella tabella da quando è stata creata, la prima riga inserita dopo l’esecuzione di DBCC CHECKIDENT utilizzerà newReseedValue come id quadro. In caso contrario, la riga successiva inserita utilizzerà newReseedValue + 1. Se il valore di newReseedValue è inferiore al valore massimo nella colonna Identity, verrà generato il messaggio di errore 2627 sui successivi riferimenti alla tabella.

Grazie a Robert per aver sottolineato il fatto che disabilitare i vincoli non consente di usare truncate, i vincoli dovrebbero essere eliminati e quindi ricreati

Ecco il re papà del database che cancella gli script. Cancellerà tutte le tabelle e le risederà correttamente:

 SET QUOTED_IDENTIFIER ON; EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? DISABLE TRIGGER ALL' EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?' EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? CHECK CONSTRAINT ALL' EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? ENABLE TRIGGER ALL' EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; IF NOT EXISTS ( SELECT * FROM SYS.IDENTITY_COLUMNS JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID WHERE SYS.TABLES.Object_ID = OBJECT_ID(''?'') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL ) AND OBJECTPROPERTY( OBJECT_ID(''?''), ''TableHasIdentity'' ) = 1 DBCC CHECKIDENT (''?'', RESEED, 0) WITH NO_INFOMSGS' 

Divertiti, ma attenzione!

Il modo più semplice per farlo è quello di

  1. aprire SQL Management Studio
  2. vai al tuo database
  3. Fai clic con il tasto destro e seleziona Attività-> Genera script (foto 1)
  4. Nella schermata “scegli oggetti”, seleziona l’opzione “seleziona oggetti specifici” e controlla “tabelle” (foto 2)
  5. nella schermata successiva, selezionare “avanzato” e quindi modificare l’opzione “Script DROP e CREATE” su “Script DROP e CREATE” (foto 3)
  6. Scegliere di salvare lo script in una nuova finestra di editor o in un file ed eseguirlo secondo necessità.

questo ti darà uno script che abbassa e ricrea tutti i tuoi tavoli senza la necessità di preoccuparti del debug o se hai incluso tutto. Mentre questo funziona più di un semplice troncamento, i risultati sono gli stessi. Tieni a mente che le tue chiavi primarie a incremento automatico inizieranno da 0, a differenza delle tabelle troncate che ricorderanno l’ultimo valore assegnato. Puoi anche eseguirlo dal codice se non hai accesso a Management Studio negli ambienti PreProd o Production.

1.

inserisci la descrizione dell'immagine qui

2.

inserisci la descrizione dell'immagine qui

3.

inserisci la descrizione dell'immagine qui

Il troncamento di tutte le tabelle funzionerà solo se non si hanno relazioni tra le chiavi esterne tra le tabelle, poiché SQL Server non consente di troncare una tabella con una chiave esterna.

Un’alternativa a ciò è determinare le tabelle con chiavi esterne ed eliminarle prima, quindi è ansible troncare le tabelle senza le chiavi esterne in seguito.

Vedere http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 e http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 per ulteriori dettagli.

Un’opzione alternativa che mi piace utilizzare con MSSQL Server Deveploper o Enterprise è creare un’istantanea del database immediatamente dopo aver creato lo schema vuoto. A quel punto puoi semplicemente ripristinare il database sullo snapshot.

Non farlo! Davvero, non è una buona idea.

Se sai quali tabelle vuoi troncare, crea una stored procedure che le tronca. È ansible correggere l’ordine per evitare problemi di chiave esterna.

Se vuoi davvero troncarli tutti (così puoi caricarli ad esempio per BCP) saresti altrettanto veloce nel lasciare il database e crearne uno nuovo da zero, il che avrebbe l’ulteriore vantaggio di sapere esattamente dove ti trovi.

Se si desidera mantenere i dati in una tabella specifica (ad esempio una tabella di ricerca statica) durante l’eliminazione / il troncamento dei dati in altre tabelle all’interno dello stesso db, è necessario un ciclo con le eccezioni al suo interno. Questo è quello che stavo cercando quando sono incappato in questa domanda.

sp_MSForEachTable sembra bug per me (ovvero un comportamento incoerente con le istruzioni IF) che è probabilmente il motivo per cui non è documentato dalla MS.

 declare @LastObjectID int = 0 declare @TableName nvarchar(100) = '' set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id]) while(@LastObjectID is not null) begin set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID) if(@TableName not in ('Profiles', 'ClientDetails', 'Addresses', 'AgentDetails', 'ChainCodes', 'VendorDetails')) begin exec('truncate table [' + @TableName + ']') end set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id]) end 

Crea un database “modello” vuoto, fai un backup completo. Quando è necessario aggiornare, è sufficiente ripristinare utilizzando WITH REPLACE. Veloce, semplice, a prova di proiettile. E se un paio di tabelle qua o là hanno bisogno di alcuni dati di base (es. Informazioni di configurazione, o solo informazioni di base che rendono l’esecuzione della tua app), gestisce anche questo.

È molto più facile (e forse anche più veloce) scrivere il tuo database, quindi lasciarlo e crearlo dallo script.

La parte più difficile del troncamento di tutte le tabelle è la rimozione e la riaggiustamento dei vincoli di chiave esterna.

La seguente query crea le istruzioni drop & create per ciascun vincolo relativo al nome di ogni tabella in @myTempTable. Se si desidera generare questi per tutte le tabelle, è ansible utilizzare lo schema di informazioni semplice per raccogliere invece questi nomi di tabelle.

 DECLARE @myTempTable TABLE (tableName varchar(200)) INSERT INTO @myTempTable(tableName) VALUES ('TABLE_ONE'), ('TABLE_TWO'), ('TABLE_THREE') -- DROP FK Contraints SELECT 'alter table '+quotename(schema_name(ob.schema_id))+ '.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name) FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id WHERE fk.referenced_object_id IN ( SELECT so.object_id FROM sys.objects so JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name IN (SELECT * FROM @myTempTable) AND sc.name=N'dbo' AND type in (N'U')) -- CREATE FK Contraints SELECT 'ALTER TABLE [PIMSUser].[dbo].[' +cast(c.name as varchar(255)) + '] WITH NOCHECK ADD CONSTRAINT ['+ cast(f.name as varchar(255)) +'] FOREIGN KEY (['+ cast(fc.name as varchar(255)) +']) REFERENCES [PIMSUser].[dbo].['+ cast(p.name as varchar(255)) +'] (['+cast(rc.name as varchar(255))+'])' FROM sysobjects f INNER JOIN sys.sysobjects c ON f.parent_obj = c.id INNER JOIN sys.sysreferences r ON f.id = r.constid INNER JOIN sys.sysobjects p ON r.rkeyid = p.id INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid WHERE f.type = 'F' AND cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable) 

Quindi copio semplicemente le istruzioni per l’esecuzione, ma con un po ‘di sforzo è ansible utilizzare un cursore per eseguirle dynamicmente.

Questo è un modo per farlo … ci sono probabilmente altri 10 che sono migliori / più efficienti, ma sembra che questo sia fatto molto di rado, quindi ecco qui …

ottenere un elenco delle tables da sysobjects , quindi sp_execsql('truncate table ' + @table_name) loop su quelli con un cursore, chiamando sp_execsql('truncate table ' + @table_name) per ogni iteration .

Non vedo perché cancellare i dati sarebbe meglio di uno script per eliminare e ricreare ogni tabella.

Quello o mantenere un backup del DB vuoto e ripristinarlo su quello vecchio

Prima di troncare le tabelle è necessario rimuovere tutte le chiavi esterne. Utilizzare questo script per generare script finali per eliminare e ricreare tutte le chiavi esterne nel database. Si prega di impostare la variabile @action su ‘CREATE’ o ‘DROP’.

Esegui una volta la sezione commentata, compila la tabella _TruncateList con le tabelle che vuoi troncare, quindi esegui il resto dello script. La tabella _ScriptLog dovrà essere ripulita nel tempo se lo si fa molto.

È ansible modificare questo se si desidera fare tutte le tabelle, basta inserire il nome SELECT INTO #TruncateList FROM sys.tables. Tuttavia, di solito non vuoi farle tutte.

Inoltre, questo influirà su tutte le chiavi esterne nel database e potrai modificarlo anche se è troppo smussato per la tua applicazione. Non è per i miei scopi.

 /* CREATE TABLE _ScriptLog ( ID Int NOT NULL Identity(1,1) , DateAdded DateTime2 NOT NULL DEFAULT GetDate() , Script NVarChar(4000) NOT NULL ) CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog ( DateAdded , ID ) CREATE TABLE _TruncateList ( TableName SysName PRIMARY KEY ) */ IF OBJECT_ID('TempDB..#DropFK') IS NOT NULL BEGIN DROP TABLE #DropFK END IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN DROP TABLE #TruncateList END IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN DROP TABLE #CreateFK END SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+ ' DROP CONSTRAINT ' + '[' + f.name + ']' INTO #DropFK FROM .sys.foreign_keys AS f INNER JOIN .sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id SELECT TableName INTO #TruncateList FROM _TruncateList SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + ' ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY ( ' + const.parent_col_csv + ' ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ') ' INTO #CreateFK FROM ( SELECT QUOTENAME(fk.NAME) AS [const_name] ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj] ,STUFF(( SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id)) FROM sys.foreign_key_columns AS fcP WHERE fcp.constraint_object_id = fk.object_id FOR XML path('') ), 1, 1, '') AS [parent_col_csv] ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj] ,STUFF(( SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id)) FROM sys.foreign_key_columns AS fcR WHERE fcR.constraint_object_id = fk.object_id FOR XML path('') ), 1, 1, '') AS [ref_col_csv] FROM sys.foreign_key_columns AS fkc INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id GROUP BY fkc.parent_object_id ,fkc.referenced_object_id ,fk.NAME ,fk.object_id ,schParent.NAME ,schRef.NAME ) AS const ORDER BY const.const_name INSERT INTO _ScriptLog (Script) SELECT Scripts FROM #CreateFK DECLARE @Cmd NVarChar(4000) , @TableName SysName WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN SELECT TOP 1 @Cmd = Scripts FROM #DropFK EXEC (@Cmd) DELETE #DropFK WHERE Scripts = @Cmd END WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' + TableName , @TableName = TableName FROM #TruncateList EXEC (@Cmd) DELETE #TruncateList WHERE TableName = @TableName END WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN SELECT TOP 1 @Cmd = Scripts FROM #CreateFK EXEC (@Cmd) DELETE #CreateFK WHERE Scripts = @Cmd END 

È un po ‘tardi ma potrebbe aiutare qualcuno. Ho creato una procedura a volte indietro che fa quanto segue usando T-SQL:

  1. Salva tutti i vincoli in una tabella temporanea
  2. Elimina tutti i vincoli
  3. Tronca tutte le tabelle con l’eccezione di alcune tabelle, che non richiedono il troncamento
  4. Ricrea tutti i vincoli.

L’ho elencato sul mio blog qui

seleziona “elimina da” + TABLE_NAME da INFORMATION_SCHEMA.TABLES dove TABLE_TYPE = ‘BASE TABLE’

dove arrivano i risultati.

Copia e incolla nella finestra di query ed esegui il comando