By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Scripting ALTER TABLE

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
"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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.