delete all data from database

Does anyone have or know of a way/script where I can delete all data from a
database following integrity rules?
Thanks
RobWhat 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
>
>|||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
>|||Thanks cristian works like a charm
would it only delete from my user tables?
"Cristian Lefter" <nospam_CristianLefter@.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...
>|||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