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.
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 |
 |
|
|
|
|