Check whether column exist or not in a table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Check whether column exist or not in a table

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-02-16 : 01:27:57

Hi, see the below queries. I want to check out whether column be exist in this table or not...
To do this, i have written 3 queires. can we join these 3 queries? if so, can we get the result of the column name which is not exist in table?

Select count(*) from Information_Schema.columns where Table_name = 'Test' and Column_name ='one'
Select count(*) from Information_Schema.columns where Table_name = 'Test' and Column_name ='two'
Select count(*) from Information_Schema.columns where Table_name = 'Test' and Column_name ='three'

G. Satish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 02:00:58

[code]select 'col1 ' + case when col_length('table1','col1') >0 then 'exists' else 'not exists'end,
'col2 ' + case when col_length('table1','col2') >0 then 'exists' else 'not exists' end,
'col3 ' + case when col_length('table1','col3') >0 then 'exists' else 'not exists' end[/code]

nishita_s
Yak Posting Veteran

61 Posts

Posted - 2009-02-16 : 02:07:40

Select
count(CASE WHEN Column_name ='one' THEN 1 ELSE NULL END)AS TestCountforOne,
count(CASE WHEN Column_name ='two'THEN 1 ELSE NULL END)AS TestCountforTwo,
count(CASE WHEN Column_name ='three'THEN 1 ELSE NULL END)AS TestCountforThree
from Information_Schema.columns where Table_name = 'Test'

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-02-16 : 02:52:42

i want to get only output if the column not exists...by using below query, we are cheking each column and displaying 1 for exist and 0 for not exist. I want output as colname which doesn;t exist in table.
Ex: col
four

quote:
Originally posted by nishita_s

Select
count(CASE WHEN Column_name ='one' THEN 1 ELSE NULL END)AS TestCountforOne,
count(CASE WHEN Column_name ='two'THEN 1 ELSE NULL END)AS TestCountforTwo,
count(CASE WHEN Column_name ='three'THEN 1 ELSE NULL END)AS TestCountforThree
from Information_Schema.columns where Table_name = 'Test'

G. Satish

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-16 : 05:07:57

u need one temp table to get the result


DECLARE	@T_Columns TABLE
(
		ColumnName NVARCHAR(128)
)
INSERT INTO @T_Columns
		SELECT 'One'
UNION ALL	SELECT 'Two'
UNION ALL	SELECT 'Three'

SELECT	C.ColumnName
FROM		@T_Columns C
LEFT JOIN	sys.syscolumns SC ON (SC.Name = C.ColumnName
						AND SC.ID = OBJECT_ID('Test'))
WHERE		SC.Name IS NULL
ORDER BY	C.ColumnName

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 05:37:19

quote:
Originally posted by satish.gorijala

i want to get only output if the column not exists...by using below query, we are cheking each column and displaying 1 for exist and 0 for not exist. I want output as colname which doesn;t exist in table.
Ex: col
four

quote:
Originally posted by nishita_s

Select
count(CASE WHEN Column_name ='one' THEN 1 ELSE NULL END)AS TestCountforOne,
count(CASE WHEN Column_name ='two'THEN 1 ELSE NULL END)AS TestCountforTwo,
count(CASE WHEN Column_name ='three'THEN 1 ELSE NULL END)AS TestCountforThree
from Information_Schema.columns where Table_name = 'Test'

G. Satish

did you try my suggetion?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources