Update all databases on a server

[DanC]

I need to add a column to an existing table in almost every database on our server (80+ db's) Not every database has the table, only about 75% of them. Is there any way using the sysdatabases table in the master db to cycle through the databases checking for the existence of the table, and if it exists, adding the column?

Thanks in advance.

 

[vadimg]

Use sp_MSforeachdb (a very, very useful undocumented SP!)

EXEC sp_MSforeachdb @command1 = 'if exists select * from sysobjects where name = "TABLE" and xtype = "U" alter table .......... '

 

[DanC]

still having a problem, the procedure seems to only be running in the currently select DB in query analyzer. This is essentially the query I'm trying to run:

EXEC sp_MSforeachdb @command1 = 'if exists (select * from dbo.sysobjects where id = object_id(N''[SYS_USER]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
INSERT INTO [SYS_USER]([USER_ID], [PASSWORD], [PREFIX], [FIRSTNAME], [MIDNAME], [LASTNAME])
VALUES(''rbonhagen'', null, ''Mr.'', '''', null, ''Bonhagen'')'

 

[tlbroadbent]

Modify the query as follows. Use ?; will set the cuurent database context to the database being processed ny sp_msforeachdb.

EXEC sp_MSforeachdb @command1 =
'Use ? ; if exists (select * from dbo.sysobjects
where id = object_id(N''[SYS_USER]'')
and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
INSERT INTO [SYS_USER]([USER_ID], [PASSWORD], [PREFIX],
[FIRSTNAME], [MIDNAME], [LASTNAME])
VALUES(''rbonhagen'', null, ''Mr.'',
'''', null, ''Bonhagen'')'
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[vadimg]

DOH!! thanks Terry

Dan, my apologies.. forgot to include that.

 

[DanC]

thanks for both of your help, it's much appreciated.