Scripting ALTER TABLE

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"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:jx1Hd.12736$5R.1377@.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|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ecd751$1_1@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:jx1Hd.12736$5R.1377@.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