Alter a table on all databases on the server

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

Transact-SQL (2008)

Alter a table on all databases on the server

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-04-07 : 17:53:56

Hi,

I am working on a server that has NTH number of databases. Each of these databases have the same table. I would like to run this statement below on all databases.


IF col_length('redemptionItem','pinVariableName') is null
	BEGIN
		ALTER TABLE [dbo].[redemptionItem]
		ADD   pinVariableName nvarchar(100) NULL  
	END
	GO

How can I do this? I know you can run this to get all DB Names:


SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-07 : 18:04:43

Take a look at sp_MSforeachdb.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-04-07 : 18:14:46

Ok so if I do something like this:

sp_msforeachdb 'ALTER TABLE [dbo].[redemptionItem] ADD pinVariableName nvarchar(100) NULL'

How can I put the IF statement in there?

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-04-08 : 09:32:28

So I assume this can't be done?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources