Scripting ALTER TABLE

[Kevin Haugen]

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in the
ALTER TABLE statement (e.g. ALTER TABLE @TBL).

Is there any reason for this?

Thanks,

Kevin

[Simon Hayes]

"Kevin Haugen" <kh*****@pacbell.net> wrote in message
news:jx*****************@newssvr21.news.prodigy.co m...

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in
the ALTER TABLE statement (e.g. ALTER TABLE @TBL).

Is there any reason for this?

Thanks,

Kevin

You can't use variables in place of table or column names, except when using
dynamic SQL, which has its own issues. Although if you're a DBA running an
admin script, then it's usually a reasonable option - there's more
discussion here:

http://www.sommarskog.se/dynamic_sql.html

Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.

Simon

[Kevin Haugen]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:41**********@news.bluewin.ch...

"Kevin Haugen" <kh*****@pacbell.net> wrote in message
news:jx*****************@newssvr21.news.prodigy.co m...

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in
the ALTER TABLE statement (e.g. ALTER TABLE @TBL).

Is there any reason for this?

Thanks,

Kevin

You can't use variables in place of table or column names, except when
using dynamic SQL, which has its own issues. Although if you're a DBA
running an admin script, then it's usually a reasonable option - there's
more discussion here:

http://www.sommarskog.se/dynamic_sql.html

Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.

Simon

I'll look into it. I'm planning on converting existing data into a new
format. Since I have to identify each table and write a query to do the
conversion, I could easily do a copy/paste for each table to disable and
re-enable the triggers and constraints. I am hoping to shortcut some of the
work by automating that piece.

Thanks,

Kevin