Add similar fields to many tables

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @.name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @.name
IF @.@.fetch_status = 0
BEGIN
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_date [DATETIME]
GO
ALTER TABLE @.name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur

I also want that if one column for a table exists; the other columns should be created rather than it quits.

Howdy!Folks, please help!
Or do i have to add the columns manually! :confused:

Howdy!|||Maybe if you are concerned about whether a column already exists it would be easier to run the four different ALTER statements separately rather than coding around possibilities...y'know, four scripts that you run against all the tables instead of one.

Just a suggestion.

But for a one-time modification like this, you have to be careful that you don't spend more time trying to devise the most optimal and elegant solution than it would take you to just knuckle down and do the dirty work.|||declare @.tablename varchar(50)
declare @.add_field varchar(255)

declare cur_tables cursor for
select name from sysobjects where type='u' and status not like '-%'

open cur_tables
fetch next from cur_tables into @.tablename
while @.@.fetch_status = 0
begin
--print 'table name is ' + @.tablename

set @.add_field = 'alter table ' + @.tablename + ' add created_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add created_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

fetch next from cur_tables into @.tablename
end
close cur_tables
deallocate cur_tables|||-- Run this and cut results to query window & run it.
-- Tim S

SET NOCOUNT ON;
SELECT
CASE WHEN col1.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_BY VARCHAR(25);' + CHAR(10) ELSE '' END +
CASE WHEN col2.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_DATE [DATETIME];'+ CHAR(10) ELSE '' END +
CASE WHEN col3.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_BY VARCHAR(25);'+ CHAR(10) ELSE '' END +
CASE WHEN col4.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_DATE [DATETIME];'+ CHAR(10) ELSE '' END
FROM INFORMATION_SCHEMA.TABLES tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col1
ON tab.TABLE_NAME = col1.TABLE_NAME AND tab.TABLE_SCHEMA = col1.TABLE_SCHEMA AND 'CREATED_BY' = col1.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col2
ON tab.TABLE_NAME = col2.TABLE_NAME AND tab.TABLE_SCHEMA = col2.TABLE_SCHEMA AND 'CREATED_DATE' = col2.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col3
ON tab.TABLE_NAME = col3.TABLE_NAME AND tab.TABLE_SCHEMA = col3.TABLE_SCHEMA AND 'MODIFIED_BY' = col3.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col4
ON tab.TABLE_NAME = col4.TABLE_NAME AND tab.TABLE_SCHEMA = col4.TABLE_SCHEMA AND 'MODIFIED_DATE' = col4.COLUMN_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE' AND -- tab.TABLE_NAME = 'test2' AND
( col1.COLUMN_NAME IS NULL OR col2.COLUMN_NAME IS NULL OR col3.COLUMN_NAME IS NULL OR col4.COLUMN_NAME IS NULL)