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

Unable to ALTER a temporary table in TSQL

P: n/a
Is it possible to ALTER a temporary table in TSQL (SQL2000)?

The following TSQL reports a syntax error at the ALTER TABLE line:

DECLARE @Test TABLE(
[Col1] [int] NOT NULL ,
[Col2] [int] NOT NULL
)
ALTER TABLE @Test
DROP COLUMN Col2

I was hoping this would work the same way as a normal table. For example:

CREATE TABLE [dbo].[Test] (
[Col1] [int] NOT NULL ,
[Col2] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Test
DROP COLUMN Col2

EXEC sp_help Test

DROP TABLE Test
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You can alter a temporary table (# or ## prefix) but not a table
variable. One of the performance benefits of table variables is reduced
recompilations and permitting DDL against a table variable would force a
recompile.

If you must execute DDL here, consider a regular temp table. However, a
better solution would be to avoid DDL against a temporary object
entirely.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Stephen Miller" <js******@hotmail.com> wrote in message
news:cd**************************@posting.google.c om...
Is it possible to ALTER a temporary table in TSQL (SQL2000)?

The following TSQL reports a syntax error at the ALTER TABLE line:

DECLARE @Test TABLE(
[Col1] [int] NOT NULL ,
[Col2] [int] NOT NULL
)
ALTER TABLE @Test
DROP COLUMN Col2

I was hoping this would work the same way as a normal table. For example:
CREATE TABLE [dbo].[Test] (
[Col1] [int] NOT NULL ,
[Col2] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Test
DROP COLUMN Col2

EXEC sp_help Test

DROP TABLE Test

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.