how to add a new column to all tables in DB

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

how to add a new column to all tables in DB

Author

Topic

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 05:39:36

i just want to add a column to all tables in DB using cursor..how we can do that?????

with regards,
Divya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 05:41:39

Write code?

sp_msforeachtable 'ALTER ? ADD x INT'

E 12°55'05.63"
N 56°04'39.26"

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 05:58:47

could you plz help me with code.....

with regards,
Divya

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 06:00:18

till now i have done like this .... because i dont want to enter each every table names in a db

declare @tblname varchar(1000)

declare cur1 cursor iter for
select name from sys.tables
open cur1
fetch next from cur1 into@tbl
while @@fetch_status=0
begin
alter table dbo.'+@tbl+'
add column 'employee id varchar(50)'
end

with regards,
Divya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-13 : 06:08:25

quote:
Originally posted by divyagr

till now i have done like this .... because i dont want to enter each every table names in a db

declare @tblname varchar(1000)

declare cur1 cursor iter for
select name from sys.tables
open cur1
fetch next from cur1 into@tbl
while @@fetch_status=0
begin
alter table dbo.'+@tbl+'
add column 'employee id varchar(50)'
end

with regards,
Divya

Peso's code would do what you want
Did you try?

Madhivanan

Failing to plan is Planning to fail

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 06:11:35

i have tried but it shows error ... as follows

Msg 155, Level 15, State 2, Line 3
'iter' is not a recognized CURSOR option.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'cur1'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '+@tbl+'.

with regards,
Divya

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-13 : 06:13:56

I assume this is done manually and personally I'd prefer something like this instead:

SELECT 'ALTER TABLE ' + table_name + ' ADD myColumn INT'
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'

I think it gives me better control of what's actually going on even though there is really not much difference.

- Lumbago

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 06:14:35

i dont know how to do with peso's code... could you help me plzzzz

with regards,
Divya

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-13 : 06:17:21

If you want to do it all in one go without copy/paste the generated code you could do like this:

DECLARE @sql nvarchar(max)

SET @sql = ''

SELECT @sql = @sql + 'ALTER TABLE ' + table_name + ' ADD myColumn INT '
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'

PRINT @sql

EXEC sp_executesql @sql

- Lumbago

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 06:20:23

hi lumbago....

thank its working.... really i m thankful to you

with regards,
Divya

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-13 : 06:21:17

Excellent :)

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 09:17:25

quote:
Originally posted by divyagr

i dont know how to do with peso's code... could you help me plzzzz

with regards,
Divya

see this as an example

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

divyagr
Starting Member

29 Posts

Posted - 2009-01-13 : 23:23:32

hi ..thank u visakh (for link you have given)

with regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 23:43:34

welcome

divyagr
Starting Member

29 Posts

Posted - 2009-01-14 : 02:09:59

hi all,
i have tried this code but this one is also shows error
use workouts
sp_MSforeachtable @command1='altertable''?'' add news int'

error is as follows

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'news'.

with regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 02:15:45

it should be

sp_MSforeachtable @command1='altertable ? add news int'

divyagr
Starting Member

29 Posts

Posted - 2009-01-14 : 02:17:28

hi visakh,

it shows error like dis

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

with regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 02:19:24

oh...you missed a space too
sp_MSforeachtable @command1='alter table ? add news int'