Altering tables with cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Altering tables with cursors

Author

Topic

shub2079
Starting Member

4 Posts

Posted - 2001-09-05 : 12:03:01

Alright, I'm trying to do something fairly off the wall. I am copying a LOT of tables from an access database into SQL Server but each table needs a some changes to be done to it. Since this is a conversion process and will probably need to be redone every couple of months I'm trying to automate the process of changing all the tables. The problem is I'm getting errors in my query and I'm not sure how to get around them. The code is as follows:


DECLARE tnames_cursor CURSOR
FOR
	SELECT TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME LIKE '%_MAIN'
OPEN tnames_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		ALTER TABLE  @tablename
		DROP  COLUMN _PRIMARY_KEY
		DROP  COLUMN _SYSTEM_NAME
		DROP  COLUMN _LAST_MODIFIED_BY
		DROP  COLUMN _FIELDS_LOADED
		DROP  COLUMN _DATETIME_LOADED
		DROP  COLUMN _FIELDS_ATTRIBUTES
		ADD COLUMN _Username char(24) PRIMARY KEY
		ALTER COLUMN _DID nvarchar(12) PRIMARY KEY
	END
	FETCH NEXT FROM tnames_cursor INTO @tablename
END

The error I'm getting is:

Server: Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near '@tablename'.

This is the '@tablename' in the ALTER TABLE line. If I can't use variables to specify tables to alter is there any other way to dynamically alter tables?

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources