Cursor to loop through all tables in db

9 Sep 2005 9:32 PM

bagman3rd

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

9 Sep 2005 9:44 PM

Soeren S. Joergensen

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

Show quote

"bagman3rd" <bagman***@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

10 Sep 2005 8:08 AM

R.D

HI
There is a straight forward method for this
google for undocumented sp_Msforeachtable command
Regards
R.D

Show quote

"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" <bagman***@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
>
>
>

10 Sep 2005 10:13 AM

David Portas

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

10 Sep 2005 10:27 AM

David Portas

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