Unable to ALTER a temporary table in TSQL

[Stephen Miller]

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

[Dan Guzman]

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