Batch procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Batch procedure

Author

Topic

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-21 : 03:14:53

Dear friends

I want to be able to make a batch that adds a column to every table in one go

Find a table object
Attach table name to columnname called "Concurrency"
Add column called Concurrency timestamp
If present already, don’t do it

Anyway to do it or do I have to do it manually?

cm

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 03:25:23

Run this in Query Analyser

select distinct 'Alter table '+table_name +' Add Concurrency TimeStamp' from Information_schema.columns
where column_name<>'Concurrency'

Copy the result back to query Analyser and run them one by one

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-21 : 03:29:47

not tested

declare @name varchar(128), @sql varchar(128)
select @name = ``
while @name < (select max(name) from sysobjects o where xtype = 'U' and not exists(select * from syscolumns c where c.id = o.id and c.name = `Concurrency`))
begin
select @name = min(name) from sysobjects o where xtype = 'U' and not exists(select * from syscolumns c where c.id = o.id and c.name = `Concurrency`) and name > @name
select @sql = `alter table ` + @name + ` add Concurrency timestamp`
exec (@sql@
end

at your own risk

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Chopsmum
Starting Member

38 Posts

Posted - 2006-04-21 : 03:41:43

Thanks Guys

I'll try after I backup

Cm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources