modify a column in all tables - 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

General SQL Server Forums

New to SQL Server Programming

modify a column in all tables

Author

Topic

tinno
Starting Member

3 Posts

Posted - 2008-08-25 : 10:03:14

hello,
i'm trying to modify the content of a column in all tables of a database if exists but i have an error when i try to run the script please need help(since new in sql 2005):

Msg 1087, Level 15, State 2, Line 13
Must declare the table variable "@str".

and this is the script that i'm trying to run:

DECLARE @TblNames Table
(COUNTER INT IDENTITY(1,1),tbl_name nvarchar(100) NULL)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT distinct TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'factor'
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
UPDATE @str SET factor = 2
SET @I = @I + 1
END

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 10:06:05

@str is declared as a variable and you're using it as a table in last update. Thats causing the error.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-08-25 : 10:56:24

Is this a one time operation? In that case you can generate your sql dynamically and then just copy/paste the result into a new query window and execute it:

SELECT DISTINCT 'UPDATE ' + TABLE_NAME + ' SET factor = 2'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'factor'

- Lumbago

tinno
Starting Member

3 Posts

Posted - 2008-08-25 : 11:22:53

Thx Lumbago for your reply,
but sorry didn't understood what you said. yes this is one time operation.
can u explain please.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-08-25 : 12:00:21

Just run the query I posted and you'll understand...

- Lumbago

tinno
Starting Member

3 Posts

Posted - 2008-08-25 : 12:17:50

thx Lunbango,
it's working, i get it.

Tinno