Alter multiple tables - 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

Script Library

Alter multiple tables

Author

Topic

PoojaSrivastava
Starting Member

2 Posts

Posted - 2010-05-09 : 10:17:51

Hi all,
I have a list of tables and i want to check if a particular column say [Col1] exists in them.
if the column does not exist in a table then alter that particular table.

I know that Cursors are used for iteration but i dnt know hw to go abt them.
I am new to cursors so any help would be appreciated.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-05-09 : 19:59:07

you can join syssolumns with sysobjects to see if the column you are looking for exists and alter those tables that dont have the column.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

PoojaSrivastava
Starting Member

2 Posts

Posted - 2010-05-09 : 23:57:01

Thanks for the reply...
can u provide me with some code for this...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-05-10 : 01:32:41

give it a shot.. look up syscolumns as well as sysobjects in books on line and do post back if you are still having trouble.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-05-10 : 01:43:34

quote:
Originally posted by PoojaSrivastava

Thanks for the reply...
can u provide me with some code for this...

This is Starter, proceed as the way you required..

Select o.id,o.name,c.id,c.name,t.name,c.length from sysobjects o
inner join syscolumns c on o.id=c.id
inner join systypes t on c.xtype =t.xtype
where o.type='u'
order by o.name,c.colid

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-10 : 07:29:06

This shpuld do it for you...but you better test it thuroughly first!!

DECLARE 
	@SQL nvarchar(2000),
	@ColName varchar(200)

SET @ColName = 'Col1'
SET @SQL = ''

SELECT @SQL = @SQL + 'ALTER TABLE [' + tab.TABLE_NAME + '] ADD [' + @ColName + '] int;' 
FROM INFORMATION_SCHEMA.TABLES tab
	LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS col
		on tab.TABLE_NAME = col.TABLE_NAME
			and tab.TABLE_SCHEMA = col.TABLE_SCHEMA
			and col.COLUMN_NAME = @ColName
WHERE col.COLUMN_NAME IS NULL

IF LEN(@SQL) > 1
	EXEC sp_executesql @SQL

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

umstorom
Starting Member

6 Posts

Posted - 2010-05-14 : 03:59:15

That's very helpful,

thanks!

http://www.developerbay.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources