can i alter a table specified by @name? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Other SQL Server Topics (2005)

can i alter a table specified by @name?

Author

Topic

dynalless
Starting Member

2 Posts

Posted - 2008-06-05 : 08:29:51

hello,
i am trying to add a column to every table in a database, if it (doesn't already exist), with the following code:

declare @name varchar(50)
declare c cursor for select name from sys.tables
open c
fetch next from c into @name
while @@FETCH_STATUS = 0
begin
if( (select count(*) from sys.columns
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = @name )
and name='ins_id') = 0 )
begin
alter table @name add ins_id int
end

fetch next from c into @name
end
close c
deallocate c

and when i run the script i get the error message
Incorrect syntax near '@name'.
reffering to the alter table statement
So, my question is, how can i actually perform the alter table in this case?
thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-06-05 : 08:53:29

If this is a onetime thing then how about generating the code with something like this?


select	'alter table ' + t.table_name + ' add ins_id int null'
from	information_schema.tables t
left outer join (
	select table_name
	from	information_schema.columns
	where	column_name = 'ins_id'
	group by table_name
	) x
		on x.table_name = t.table_name
where	t.table_type = 'base table'
--EDIT: forgot to add this:
and     x.table_name is null

Be One with the Optimizer
TG

CTDBA
Starting Member

9 Posts

Posted - 2008-06-10 : 16:17:45

DECLARE @databaseName sysname
DECLARE @rowCnt int

CREATE TABLE #userDatabases (databaseName char(50))
Insert Into #userDatabases(databaseName)
Select [name] from master..sysdatabases where
[name] NOT IN ('tempdb', 'master','model','msdb')

SELECT TOP 1 @databaseName = databaseName
FROM #userDatabases

SET @rowCnt = @@ROWCOUNT

WHILE @rowCnt <> 0
BEGIN
alter table @databaseName add ins_id int
DELETE FROM #userDatabases
WHERE @databaseName = databaseName
SELECT TOP 1 @databaseName = databaseName
FROM #UserDatabases
SET @rowCnt = @@ROWCOUNT
END
drop table #userDatabases

dynalless
Starting Member

2 Posts

Posted - 2008-06-11 : 02:24:41

thank you for your posts. i solved the problem by putting the alter table statement in a varchar variable and then applying exec to it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources