Vidage de tables

Fred BROUARD

08/12/2004 à 09:41

TRUNCATE

A +

borland a écrit:

Comment pourrais-je vider toutes les tables d'une base de données, sachant
qu'il y a des relations d'intégrités entre les tables.

--
Frédéric BROUARD, MVP SQL Server. Expert SQL / spécialiste Delphi, web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
************************ www.datasapiens.com *************************

bruno reiter [MVP]

08/12/2004 à 10:45

il faut les vider dans l'ordre, fonction des FK
ou bien scripter les Suppressions et création des FK,
supprimer les FK
TRUNCATE TABLE
recréer les FK

HTH

br

"borland" wrote in message
news:

Comment pourrais-je vider toutes les tables d'une base de données, sachant
qu'il y a des relations d'intégrités entre les tables.

Philippe T [MS]

09/12/2004 à 21:15

Bonjour,

Voici une procédure qui commence par trier les tables et ensuite purge le
tout !!!

/*--------------------------------------------------------------------------
------------------------------------------------
<StoredProcedure name="tp_PurgeAllTables">

<Object> That store procedure could be used to purge all tables of a
database </Object>

<History Author = "Philippe TROTIN - " Date "23/09/2003"> </History>

</StoredProcedure>
----------------------------------------------------------------------------
-----------------------------------------------*/
CREATE PROCEDURE tp_PurgeAllTables
AS
BEGIN
SET NOCOUNT ON

---------------------------------------------------------------------------
------------------
-- Order all tables (checking dependencies)
---------------------------------------------------------------------------
------------------
DECLARE @MyTable table(TBL_NAME nvarchar(200), TBL_ORDER int)
DECLARE @OrderNumber int
DECLARE @Continue int

-- Init tables list
INSERT INTO @MyTable(TBL_NAME, TBL_ORDER)
SELECT name, 0 FROM Sysobjects where type = 'U' AND name <> 'dtproperties'

-- Parse tables to order them
SET @OrderNumber = 0
SET @Continue = 1
WHILE @Continue = 1
BEGIN
-- Increment order number
SET @OrderNumber = @OrderNumber + 1

-- To check dependencies
UPDATE @MyTable
SET TBL_ORDER = @OrderNumber
FROM @MyTable
WHERE TBL_ORDER = 0 AND TBL_NAME COLLATE database_default NOT IN
(
SELECT DISTINCT s1.name
FROM Sysobjects s1
INNER JOIN SysForeignKeys sfk ON s1.id = sfk.fkeyid
INNER JOIN Sysobjects s2 ON s2.id = sfk.rkeyid
INNER JOIN @MyTable my ON s2.name = my.TBL_NAME COLLATE database_default
WHERE s1.type = 'U' AND s2.type = 'U' AND my.TBL_ORDER = 0 AND s1.name <>
s2.name
)

-- To stop the loop
IF NOT EXISTS(SELECT * FROM @MyTable WHERE TBL_ORDER = 0) OR @OrderNumber

100

BEGIN
SET @Continue = 0
END
END

---------------------------------------------------------------------------
------------------
-- Parse all tables and delete content
---------------------------------------------------------------------------
------------------
DECLARE @cur1_TBL_NAME nvarchar(100)
DECLARE @SQL nvarchar(200)
DECLARE @ERR_MESSAGE nvarchar(200)

DECLARE Cursor_Tables CURSOR FOR
SELECT TBL_NAME FROM @MyTable ORDER BY TBL_ORDER DESC

OPEN Cursor_Tables
FETCH NEXT FROM Cursor_Tables INTO @cur1_TBL_NAME

-- Purge each table in the Cursor
WHILE (@@fetch_status = 0)
BEGIN
SET @SQL = 'DELETE FROM ' + @cur1_TBL_NAME
EXEC (@SQL)

IF (@@ERROR <> 0)
BEGIN
SET @ERR_MESSAGE = 'ERROR : Table ' + @cur1_TBL_NAME+ ' could not be
purge'
RAISERROR (@ERR_MESSAGE, 16, 1)
END

FETCH NEXT FROM Cursor_Tables INTO @cur1_TBL_NAME
END

CLOSE Cursor_Tables
DEALLOCATE Cursor_Tables

END
GO

--

Phil.
________________________________________________________
Philippe TROTIN http://blogs.msdn.com/ptrotin
Microsoft Services France http://www.microsoft.com/france

"borland" wrote in message
news:

Comment pourrais-je vider toutes les tables d'une base de données, sachant
qu'il y a des relations d'intégrités entre les tables.

Bonjour,

Voici une procédure qui commence par trier les tables et ensuite purge le
tout !!!

/*--------------------------------------------------------------------------
------------------------------------------------
<StoredProcedure name="tp_PurgeAllTables">

<Object> That store procedure could be used to purge all tables of a
database </Object>

<History Author = "Philippe TROTIN - ptrotin@microsoft.com" Date "23/09/2003"> </History>

</StoredProcedure>
----------------------------------------------------------------------------
-----------------------------------------------*/
CREATE PROCEDURE tp_PurgeAllTables
AS
BEGIN
SET NOCOUNT ON

---------------------------------------------------------------------------
------------------
-- Order all tables (checking dependencies)
---------------------------------------------------------------------------
------------------
DECLARE @MyTable table(TBL_NAME nvarchar(200), TBL_ORDER int)
DECLARE @OrderNumber int
DECLARE @Continue int

-- Init tables list
INSERT INTO @MyTable(TBL_NAME, TBL_ORDER)
SELECT name, 0 FROM Sysobjects where type = 'U' AND name <> 'dtproperties'

-- Parse tables to order them
SET @OrderNumber = 0
SET @Continue = 1
WHILE @Continue = 1
BEGIN
-- Increment order number
SET @OrderNumber = @OrderNumber + 1

-- To check dependencies
UPDATE @MyTable
SET TBL_ORDER = @OrderNumber
FROM @MyTable
WHERE TBL_ORDER = 0 AND TBL_NAME COLLATE database_default NOT IN
(
SELECT DISTINCT s1.name
FROM Sysobjects s1
INNER JOIN SysForeignKeys sfk ON s1.id = sfk.fkeyid
INNER JOIN Sysobjects s2 ON s2.id = sfk.rkeyid
INNER JOIN @MyTable my ON s2.name = my.TBL_NAME COLLATE database_default
WHERE s1.type = 'U' AND s2.type = 'U' AND my.TBL_ORDER = 0 AND s1.name <>
s2.name
)

-- To stop the loop
IF NOT EXISTS(SELECT * FROM @MyTable WHERE TBL_ORDER = 0) OR @OrderNumber

100

BEGIN
SET @Continue = 0
END
END

---------------------------------------------------------------------------
------------------
-- Parse all tables and delete content
---------------------------------------------------------------------------
------------------
DECLARE @cur1_TBL_NAME nvarchar(100)
DECLARE @SQL nvarchar(200)
DECLARE @ERR_MESSAGE nvarchar(200)

DECLARE Cursor_Tables CURSOR FOR
SELECT TBL_NAME FROM @MyTable ORDER BY TBL_ORDER DESC

OPEN Cursor_Tables
FETCH NEXT FROM Cursor_Tables INTO @cur1_TBL_NAME

-- Purge each table in the Cursor
WHILE (@@fetch_status = 0)
BEGIN
SET @SQL = 'DELETE FROM ' + @cur1_TBL_NAME
EXEC (@SQL)

IF (@@ERROR <> 0)
BEGIN
SET @ERR_MESSAGE = 'ERROR : Table ' + @cur1_TBL_NAME+ ' could not be
purge'
RAISERROR (@ERR_MESSAGE, 16, 1)
END

FETCH NEXT FROM Cursor_Tables INTO @cur1_TBL_NAME
END

CLOSE Cursor_Tables
DEALLOCATE Cursor_Tables

END
GO

--

Phil.
________________________________________________________
Philippe TROTIN http://blogs.msdn.com/ptrotin
Microsoft Services France http://www.microsoft.com/france

"borland" <tadjeddine@hotmail.com> wrote in message
news:eOXaWmP3EHA.2180@TK2MSFTNGP10.phx.gbl...

Comment pourrais-je vider toutes les tables d'une base de données, sachant
qu'il y a des relations d'intégrités entre les tables.

Vous avez filtré cet utilisateur ! Consultez son message

Bonjour,

Voici une procédure qui commence par trier les tables et ensuite purge le
tout !!!

/*--------------------------------------------------------------------------
------------------------------------------------
<StoredProcedure name="tp_PurgeAllTables">

<Object> That store procedure could be used to purge all tables of a
database </Object>

<History Author = "Philippe TROTIN - " Date "23/09/2003"> </History>

</StoredProcedure>
----------------------------------------------------------------------------
-----------------------------------------------*/
CREATE PROCEDURE tp_PurgeAllTables
AS
BEGIN
SET NOCOUNT ON

---------------------------------------------------------------------------
------------------
-- Order all tables (checking dependencies)
---------------------------------------------------------------------------
------------------
DECLARE @MyTable table(TBL_NAME nvarchar(200), TBL_ORDER int)
DECLARE @OrderNumber int
DECLARE @Continue int

-- Init tables list
INSERT INTO @MyTable(TBL_NAME, TBL_ORDER)
SELECT name, 0 FROM Sysobjects where type = 'U' AND name <> 'dtproperties'

-- Parse tables to order them
SET @OrderNumber = 0
SET @Continue = 1
WHILE @Continue = 1
BEGIN
-- Increment order number
SET @OrderNumber = @OrderNumber + 1

-- To check dependencies
UPDATE @MyTable
SET TBL_ORDER = @OrderNumber
FROM @MyTable
WHERE TBL_ORDER = 0 AND TBL_NAME COLLATE database_default NOT IN
(
SELECT DISTINCT s1.name
FROM Sysobjects s1
INNER JOIN SysForeignKeys sfk ON s1.id = sfk.fkeyid
INNER JOIN Sysobjects s2 ON s2.id = sfk.rkeyid
INNER JOIN @MyTable my ON s2.name = my.TBL_NAME COLLATE database_default
WHERE s1.type = 'U' AND s2.type = 'U' AND my.TBL_ORDER = 0 AND s1.name <>
s2.name
)

-- To stop the loop
IF NOT EXISTS(SELECT * FROM @MyTable WHERE TBL_ORDER = 0) OR @OrderNumber

100

BEGIN
SET @Continue = 0
END
END

---------------------------------------------------------------------------
------------------
-- Parse all tables and delete content
---------------------------------------------------------------------------
------------------
DECLARE @cur1_TBL_NAME nvarchar(100)
DECLARE @SQL nvarchar(200)
DECLARE @ERR_MESSAGE nvarchar(200)

DECLARE Cursor_Tables CURSOR FOR
SELECT TBL_NAME FROM @MyTable ORDER BY TBL_ORDER DESC

OPEN Cursor_Tables
FETCH NEXT FROM Cursor_Tables INTO @cur1_TBL_NAME

-- Purge each table in the Cursor
WHILE (@@fetch_status = 0)
BEGIN
SET @SQL = 'DELETE FROM ' + @cur1_TBL_NAME
EXEC (@SQL)

IF (@@ERROR <> 0)
BEGIN
SET @ERR_MESSAGE = 'ERROR : Table ' + @cur1_TBL_NAME+ ' could not be
purge'
RAISERROR (@ERR_MESSAGE, 16, 1)
END

FETCH NEXT FROM Cursor_Tables INTO @cur1_TBL_NAME
END

CLOSE Cursor_Tables
DEALLOCATE Cursor_Tables

END
GO

--

Phil.
________________________________________________________
Philippe TROTIN http://blogs.msdn.com/ptrotin
Microsoft Services France http://www.microsoft.com/france

"borland" wrote in message
news:

Comment pourrais-je vider toutes les tables d'une base de données, sachant
qu'il y a des relations d'intégrités entre les tables.

Vidage de tables

3 réponses

Veuillez sélectionner un problème