MS SQL Server - delete all data from database

Author

delete all data from database

Rob Blij

2005-06-06, 11:23 am

Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?

Thanks
Rob

Alejandro Mesa

2005-06-06, 11:23 am

What about dropping the database and recreating it?

AMB

"Rob Blij" wrote:

> Does anyone have or know of a way/script where I can delete all data from a
> database following integrity rules?
>
> Thanks
> Rob
>
>
>

Cristian Lefter

2005-06-06, 11:23 am

Without referential integrity
[code]
use MyDatabase
go
exec sp_MSforeachtable "alter table ? nocheck constraint all"
exec sp_MSforeachtable "delete from ?"
exec sp_MSforeachtable "alter table ? check constraint all"
[/code]

Cristian Lefter, SQL Server MVP

"Rob Blij" <robblij@community.nospam> wrote in message
news:OKXB6dqaFHA.1044@TK2MSFTNGP10.phx.gbl...
> Does anyone have or know of a way/script where I can delete all data from
> a database following integrity rules?
>
> Thanks
> Rob
>

Rob Blij

2005-06-07, 7:23 am

Thanks cristian works like a charm

would it only delete from my user tables?

"Cristian Lefter" < nospam_CristianLefte
r@hotmail.com> wrote in message
news:%23KVKwuqaFHA.2124@TK2MSFTNGP14.phx.gbl...
> Without referential integrity
> [code]
> use MyDatabase
> go
> exec sp_MSforeachtable "alter table ? nocheck constraint all"
> exec sp_MSforeachtable "delete from ?"
> exec sp_MSforeachtable "alter table ? check constraint all"
> [/code]
>
> Cristian Lefter, SQL Server MVP
>
> "Rob Blij" <robblij@community.nospam> wrote in message
> news:OKXB6dqaFHA.1044@TK2MSFTNGP10.phx.gbl...
>
>

Anthony Thomas

2005-06-19, 3:23 am

You could certainly query the catalogue to generate the RI; however, you
should never delete the data without knowing the design of the "logical data
model" (LDB).

You could use the Database Digram tool to generate a graphic or a
third-party tool that could generate a model, but this would only be a
"physical data model" (PDM).

From this you could "infer" an LDM, but without a real one, there would be
no gaurantees.

If you are lucky, that is, the database designer used Declaritive RI and
other contraints exclusively. If you ran the sp_MSforeachtable script, then
only the child table data would succeed without error. Then, you could run
again to get the next level. When you could run the script without error,
you'd be done.

Sincerely,

Anthony Thomas

--

"Rob Blij" <robblij@community.nospam> wrote in message
news:OKXB6dqaFHA.1044@TK2MSFTNGP10.phx.gbl...
Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?

Thanks
Rob

Sponsored Links