Cursor to loop through all tables in db

So far, I have avoided using cursors, but I see no other way to do this.
I have a db with 285 tables. I want to add 1 varchar(10) column to all 285
tables called 'year' and I always want year to be '1999'.
I am struggling with setting up the cursor though. This is what I have so
far:
declare table_loop cursor
For select name from sysobjects where xtype = 'u'
open table_loop
Fetch table_loop
alter table [name]
add db_year varchar(10)
close table_loop
deallocate table_loop
go
Any help would be greatly appreciated.
Thanks.
ArcherIt has to be something like
declare @.name varchar(')
declare table_loop cursor for select name from sysobjects where xtype = 'u'
open table_loop
fetch next from table_loop into @.Name
while @.@.FETCH_STATUS = 0
begin
-- this migth have to be some dynamic thing, like:
-- exec 'alter table ' + @.name + ' add db_year varchar(10)'
alter table [@.name]
add db_year varchar(10)
fetch next from table_loop into @.name
end
close table_loop
deallocate table_loop
go
Kr.
Soren
"bagman3rd" <bagman3rd@.discussions.microsoft.com> skrev i en meddelelse
news:24C3F48A-4961-4285-9FDF-F790961789C5@.microsoft.com...
> So far, I have avoided using cursors, but I see no other way to do this.
> I have a db with 285 tables. I want to add 1 varchar(10) column to all
> 285
> tables called 'year' and I always want year to be '1999'.
> I am struggling with setting up the cursor though. This is what I have so
> far:
> declare table_loop cursor
> For select name from sysobjects where xtype = 'u'
> open table_loop
> Fetch table_loop
> alter table [name]
> add db_year varchar(10)
> close table_loop
> deallocate table_loop
> go
> Any help would be greatly appreciated.
> Thanks.
> Archer|||HI
There is a straight forward method for this
google for undocumented sp_Msforeachtable command
Regards
R.D
"Soeren S. Joergensen" wrote:

> It has to be something like
> declare @.name varchar(')
> declare table_loop cursor for select name from sysobjects where xtype = 'u
'
> open table_loop
> fetch next from table_loop into @.Name
> while @.@.FETCH_STATUS = 0
> begin
> -- this migth have to be some dynamic thing, like:
> -- exec 'alter table ' + @.name + ' add db_year varchar(10)'
> alter table [@.name]
> add db_year varchar(10)
> fetch next from table_loop into @.name
> end
> close table_loop
> deallocate table_loop
> go
> Kr.
> Soren
> "bagman3rd" <bagman3rd@.discussions.microsoft.com> skrev i en meddelelse
> news:24C3F48A-4961-4285-9FDF-F790961789C5@.microsoft.com...
>
>|||Wouldn't you be better off with a DDL script that you can check into source
control and deploy to test and live environments? Try this:
SELECT
'ALTER TABLE '+QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)+
' ADD [year_num] VARCHAR(10) NOT NULL'+
' CONSTRAINT [df_year_num] DEFAULT (''1999'')'
FROM information_schema.tables ;
Output as text and you have your script.
Note that "YEAR" is a reserved word so not a good choice for a column name.
Why make the year number a VARCHAR anyway?
David Portas
SQL Server MVP
--|||CORRECTION:
SELECT
'ALTER TABLE '+QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)+
' ADD [year_num] VARCHAR(10) NOT NULL'+
' CONSTRAINT [df_'+table_name+'_year_num] DEFAULT (''1999'')'
FROM information_schema.tables ;
David Portas
SQL Server MVP
--sql