Comment tronquer toutes les tables D'une base de données en utilisant TSQL?

lorsqu'il s'agit de supprimer des données de tables qui ont des relations clés étrangères - ce qui est essentiellement le cas de toute base de données correctement conçue - nous pouvons désactiver toutes les contraintes, supprimer toutes les données et ensuite réactiver les contraintes

-- 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"

pour en savoir plus sur les contraintes et les déclencheurs de désactivation ici

si certains tableaux ont des colonnes d'identité nous voulons pour réensemencer

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

noter que le comportement de la recharge diffère entre un tableau Tout Neuf et un tableau qui avait fait insérer des données précédemment de BOL :

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)

la valeur d'identité actuelle est définie à the newReseedValue. Si aucune ligne n'a été insérée dans la table car il a été créé, la première ligne insérée après l'exécution de DBCC CHECKIDENT will utilisez newReseedValue comme l'identité. Sinon, la ligne suivante insérée utilisez newReseedValue + 1. Si la valeur de newReseedValue est inférieure à la valeur maximale dans la colonne identité, message d'erreur 2627 sera généré sur les références ultérieures au tableau.

merci à Robert pour avoir souligné le fait que les contraintes invalidantes ne permettent pas d'utiliser truncate, les contraintes devraient être abandonné, puis recréé

voici le roi des scripts d'effacement de bases de données. Il va effacer toutes les tables et les réensemencer correctement:

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'

Profiter, mais attention!

la façon la plus simple de faire ceci est de

1. ouvrez SQL server Management Studio
2. naviguez vers votre base de données
3. clic droit et sélectionner les tâches- > générer des Scripts (pic 1)
4. sur l'écran "Choisir des objets", sélectionnez l'option "Sélectionner des objets spécifiques" et cochez " tables "(pic 2)
5. sur l'écran suivant, sélectionnez "Avancé", puis modifiez le "script DROP and CREATE"." option " Script DROP and CREATE "(pic 3)
6. choisissez de sauvegarder le script dans une nouvelle fenêtre d'éditeur ou dans un fichier et lancez-le si nécessaire.

cela vous donnera un script qui tombe et recrée toutes vos tables sans avoir à vous soucier du débogage ou si vous avez tout inclus. Bien que cela accomplisse plus qu'un simple tronqué, les résultats sont les mêmes. Il suffit de garder à l'esprit que vos clés primaires auto-incrémentant commencera à 0, par opposition à tables tronquées qui se souviendront de la dernière Valeur assignée. Vous pouvez également l'exécuter à partir du code si vous n'avez pas accès à Management studio sur vos environnements de Préprod ou de Production.

1.

2.

3.

tronquer toutes les tables ne fonctionnera que si vous n'avez aucune relation clé étrangère entre vos tables, car SQL Server ne vous permettra pas de tronquer une table avec une clé étrangère.

une alternative à cela est de déterminer les tables avec des clés étrangères et de supprimer de celles-ci d'abord, vous pouvez ensuite tronquer les tables sans clés étrangères.

voir http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 et http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 pour plus de détails.

une option alternative que j'aime utiliser avec MSSQL Deveploper Server ou Enterprise est de créer un instantané de la base de données immédiatement après avoir créé le schéma vide. À ce moment-là, vous pouvez simplement continuer à restaurer la base de données vers le snapshot.

ne faites pas ça! Vraiment, pas une bonne idée.

si vous savez quelles tables vous voulez tronquer, créez une procédure stockée qui les tronque. Vous pouvez fixer l'ordre pour éviter de clé étrangère problèmes.

si vous voulez vraiment les tronquer tous (donc vous pouvez les charger par BCP par exemple) vous seriez tout aussi rapide à laisser tomber la base de données et en créer une nouvelle à partir de zéro, ce qui aurait l'avantage supplémentaire que vous savez exactement où vous êtes.

si vous voulez garder des données dans une table particulière (c.-à-d. une table de recherche statique) tout en supprimant/tronquant des données dans d'autres tables dans le même db, alors vous avez besoin d'une boucle avec les exceptions dans elle. C'est ce que je cherchais quand je suis tombé sur cette question.

sp_MSForEachTable me semble buggy (i.e. inconsistent behavior with IF statements) which is probably why its undocumented by 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

faire une base de données" template " vide, faire une sauvegarde complète. Lorsque vous avez besoin de rafraîchir, il suffit de restaurer l'utilisation avec remplacer. Rapide, simple, à l'épreuve des balles. Et si quelques tables ici ou là ont besoin de quelques données de base(par exemple des informations de configuration, ou juste des informations de base qui font fonctionner votre application), cela fonctionne aussi.

il est beaucoup plus facile (et peut-être même plus rapide) de script sur votre base de données, puis il suffit de laisser tomber et de créer à partir du script.

la partie la plus difficile de la troncature de toutes les tables est d'enlever et de modifier les contraintes de clé étrangère.

la requête suivante crée le drop & create des instructions pour chaque contrainte relative à chaque nom de table dans @mistemptable. Si vous souhaitez générer ceux-ci pour toutes les tables, vous pouvez simplement utiliser le schéma d'information pour rassembler ces noms de table à la place.

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)

j'ai alors juste copier les déclarations à exécuter - mais avec un peu de dev effort vous pouvez utiliser un curseur pour les exécuter dynamiquement.

C'est un façon de le faire... il y en a probablement 10 autres qui sont meilleurs / plus efficaces, mais il semble que ce soit fait très rarement, alors voilà...

obtenir une liste du tables de sysobjects , puis boucler ceux avec un curseur, appelant sp_execsql('truncate table ' + @table_name) pour chaque iteration .

je ne vois pas pourquoi effacement des données serait mieux qu'un script pour supprimer et recréer chaque table.

Que ou de conserver une sauvegarde de votre vide DB et de restauration plus ancien

avant de tronquer les tables vous devez enlever toutes les clés étrangères. Utilisez ce script pour générer des scripts finaux pour supprimer et recréer toutes les clés étrangères dans la base de données. Veuillez définir la variable @action à 'CREATE'ou ' DROP'.

exécutez la section commentée une fois, peuplez la table _TruncateList avec les tables que vous voulez tronquées, puis lancez le reste du script. La table _ScriptLog devra être nettoyée au fil du temps si vous faites cela beaucoup.

vous pouvez modifier ceci si vous voulez faire toutes les tables, il suffit de mettre le nom de SELECT dans #TruncateList de sys.table. Cependant, vous ne voulez généralement pas les faire tous.

aussi, cela affectera toutes les clés étrangères dans le base de données, et vous pouvez modifier cela aussi bien si elle est trop émoussée pour votre application. Ce n'est pas pour mes fins.

/*
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

c'est un peu tard mais ça pourrait aider quelqu'un. J'ai créé une procédure parfois back qui fait ce qui suit en utilisant T-SQL:

1. stocker toutes les contraintes dans une table temporaire
2. Drop Toutes Les Contraintes
3. tronquer tous les tableaux à l'exception de certains, qui ne nécessite pas de troncature
4. recréer toutes les contraintes.

Je l'ai listé sur mon blog ici

sélectionnez "Supprimer de" +TABLE_NAME from INFORMATION_SCHEMA.Tableaux où TABLE_TYPE= 'table de BASE '

où le résultat vient.

copier-coller sur la fenêtre de requête et exécuter la commande