Prev: query help
Next: Dynamic SQL , Discussion about Erland’s Article
From: Eric Russell on 1 Jul 2008 15:21
Sure, you can do it all with a single command.
Hold it with both hands, and be careful where you point that thing, kid.

exec sp_MSforeachtable
'
ALTER TABLE ? NOCHECK CONSTRAINT ALL;
ALTER TABLE ? DISABLE TRIGGER ALL;
TRUNCATE TABLE ?;
INSERT INTO ? SELECT * FROM ServerName\DBName.SchemaName.?;
ALTER TABLE ? CHECK CONSTRAINT ALL;
ALTER TABLE ? ENABLE TRIGGER ALL;
'


"Rajesh Kallakuri" wrote:

> I want to drop all the tables and in a database and repopulate with the tables exist in the other server.
> What is the best method that i can do?
>
> While i am trying to drop the object i am getting error if it has dependencies how and i drop all the tables including all the dependicies.
>
>
From: Alex Kuznetsov on 1 Jul 2008 16:13
On Jul 1, 2:21 pm, Eric Russell
<EricRuss...(a)discussions.microsoft.com> wrote:
> Sure, you can do it all with a single command.
> Hold it with both hands, and be careful where you point that thing, kid.
>
> exec sp_MSforeachtable
> '
> ALTER TABLE ? NOCHECK CONSTRAINT ALL;
> ALTER TABLE ? DISABLE TRIGGER ALL;
> TRUNCATE TABLE ?;
> INSERT INTO ? SELECT * FROM ServerName\DBName.SchemaName.?;
> ALTER TABLE ? CHECK CONSTRAINT ALL;
> ALTER TABLE ? ENABLE TRIGGER ALL;
> '
>
> "Rajesh Kallakuri" wrote:
> > I want to drop all the tables and in a database and repopulate with the tables exist in the other server.
> > What is the best method that i can do?
>
> > While i am trying to drop the object i am getting error if it has dependencies how and i drop all the tables including all the dependicies.

I'm a little bit skeptical - what if sp_MSforeachtable runs your
script against a parent table before truncating the child one?
From: Eric Russell on 1 Jul 2008 16:34
You have a point, it really should be more like this:

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL;'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL;'
exec sp_MSforeachtable 'TRUNCATE TABLE ?;'
exec sp_MSforeachtable 'INSERT INTO ? SELECT * FROM
ServerName\DBName.SchemaName.?;'
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL;'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL;'


"Alex Kuznetsov" wrote:

> On Jul 1, 2:21 pm, Eric Russell
> <EricRuss...(a)discussions.microsoft.com> wrote:
> > Sure, you can do it all with a single command.
> > Hold it with both hands, and be careful where you point that thing, kid.
> >
> > exec sp_MSforeachtable
> > '
> > ALTER TABLE ? NOCHECK CONSTRAINT ALL;
> > ALTER TABLE ? DISABLE TRIGGER ALL;
> > TRUNCATE TABLE ?;
> > INSERT INTO ? SELECT * FROM ServerName\DBName.SchemaName.?;
> > ALTER TABLE ? CHECK CONSTRAINT ALL;
> > ALTER TABLE ? ENABLE TRIGGER ALL;
> > '
> >
> > "Rajesh Kallakuri" wrote:
> > > I want to drop all the tables and in a database and repopulate with the tables exist in the other server.
> > > What is the best method that i can do?
> >
> > > While i am trying to drop the object i am getting error if it has dependencies how and i drop all the tables including all the dependicies.
>
> I'm a little bit skeptical - what if sp_MSforeachtable runs your
> script against a parent table before truncating the child one?
>
From: Tibor Karaszi on 2 Jul 2008 01:06
PMFJI (in case I've missed something earlier in this thread), but disabling a FK constraint for a
referenced table doesn't allow for TRUNCATE TABLE. Whenever I see requests like these ("scratch a
database" I keep wondering why not just drop the database and re-create it using the build scripts
(DDL files). And I also feel like not having build scripts is not an option - like having an exe
file you developed but you no longer have the source code...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Eric Russell" <EricRussell(a)discussions.microsoft.com> wrote in message
news:BE5D2E27-D98B-447B-ADD5-F28D0A9D8C01(a)microsoft.com...
> You have a point, it really should be more like this:
>
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL;'
> exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL;'
> exec sp_MSforeachtable 'TRUNCATE TABLE ?;'
> exec sp_MSforeachtable 'INSERT INTO ? SELECT * FROM
> ServerName\DBName.SchemaName.?;'
> exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL;'
> exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL;'
>
>
> "Alex Kuznetsov" wrote:
>
>> On Jul 1, 2:21 pm, Eric Russell
>> <EricRuss...(a)discussions.microsoft.com> wrote:
>> > Sure, you can do it all with a single command.
>> > Hold it with both hands, and be careful where you point that thing, kid.
>> >
>> > exec sp_MSforeachtable
>> > '
>> > ALTER TABLE ? NOCHECK CONSTRAINT ALL;
>> > ALTER TABLE ? DISABLE TRIGGER ALL;
>> > TRUNCATE TABLE ?;
>> > INSERT INTO ? SELECT * FROM ServerName\DBName.SchemaName.?;
>> > ALTER TABLE ? CHECK CONSTRAINT ALL;
>> > ALTER TABLE ? ENABLE TRIGGER ALL;
>> > '
>> >
>> > "Rajesh Kallakuri" wrote:
>> > > I want to drop all the tables and in a database and repopulate with the tables exist in the
>> > > other server.
>> > > What is the best method that i can do?
>> >
>> > > While i am trying to drop the object i am getting error if it has dependencies how and i drop
>> > > all the tables including all the dependicies.
>>
>> I'm a little bit skeptical - what if sp_MSforeachtable runs your
>> script against a parent table before truncating the child one?
>>

From: Eric Russell on 2 Jul 2008 10:19
The "'TRUNCATE TABLE ?;" part can be replaced with "DELETE * FROM ?;"

I'm assuming that his purpose of this operation would be to quickly re-stage
a Dev or Unit Test environment with data. It would of course have absolutely
no place in a real QA or Production environment.


"Tibor Karaszi" wrote:

> PMFJI (in case I've missed something earlier in this thread), but disabling a FK constraint for a
> referenced table doesn't allow for TRUNCATE TABLE. Whenever I see requests like these ("scratch a
> database" I keep wondering why not just drop the database and re-create it using the build scripts
> (DDL files). And I also feel like not having build scripts is not an option - like having an exe
> file you developed but you no longer have the source code...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Eric Russell" <EricRussell(a)discussions.microsoft.com> wrote in message
> news:BE5D2E27-D98B-447B-ADD5-F28D0A9D8C01(a)microsoft.com...
> > You have a point, it really should be more like this:
> >
> > exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL;'
> > exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL;'
> > exec sp_MSforeachtable 'TRUNCATE TABLE ?;'
> > exec sp_MSforeachtable 'INSERT INTO ? SELECT * FROM
> > ServerName\DBName.SchemaName.?;'
> > exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL;'
> > exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL;'
> >
> >
> > "Alex Kuznetsov" wrote:
> >
> >> On Jul 1, 2:21 pm, Eric Russell
> >> <EricRuss...(a)discussions.microsoft.com> wrote:
> >> > Sure, you can do it all with a single command.
> >> > Hold it with both hands, and be careful where you point that thing, kid.
> >> >
> >> > exec sp_MSforeachtable
> >> > '
> >> > ALTER TABLE ? NOCHECK CONSTRAINT ALL;
> >> > ALTER TABLE ? DISABLE TRIGGER ALL;
> >> > TRUNCATE TABLE ?;
> >> > INSERT INTO ? SELECT * FROM ServerName\DBName.SchemaName.?;
> >> > ALTER TABLE ? CHECK CONSTRAINT ALL;
> >> > ALTER TABLE ? ENABLE TRIGGER ALL;
> >> > '
> >> >
> >> > "Rajesh Kallakuri" wrote:
> >> > > I want to drop all the tables and in a database and repopulate with the tables exist in the
> >> > > other server.
> >> > > What is the best method that i can do?
> >> >
> >> > > While i am trying to drop the object i am getting error if it has dependencies how and i drop
> >> > > all the tables including all the dependicies.
> >>
> >> I'm a little bit skeptical - what if sp_MSforeachtable runs your
> >> script against a parent table before truncating the child one?
> >>
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: query help
Next: Dynamic SQL , Discussion about Erland’s Article