finding a column ?????? - 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

New to SQL Server Programming

finding a column ??????

Author

Topic

SanjaySutar
Starting Member

18 Posts

Posted - 2008-05-02 : 21:29:41

Hi,
How to find if a column exists in Database Table ?

thanx in advance

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-02 : 21:56:11

SELECT * FROM INFORMATION_SCHEMA.COLUMN WHERE TABLE_NAME = 'yourtable' AND COLUMN_NAME = 'desiredcolumn'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 01:24:08

or if its sql 2000 you can use

SELECT *
FROM syscolumns c
INNER JOIN sysobjects o
ON o.Id=c.Id
WHERE o.name = 'YourTableName'
AND c.name ='YourColumnName'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-03 : 01:31:16

You can use the query against INFORMATION_SCHEMA.COLUMN in any version of SQL Server from version 7.0 on.

CODO ERGO SUM

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-05-03 : 02:06:08

When I run SELECT * FROM INFORMATION_SCHEMA.COLUMN WHERE TABLE_NAME = 'JOB' AND COLUMN_NAME = 'JOB_ID' , I get "Incorrect syntax near the keyword 'COLUMN'."

Following runs fine and provides information

SELECT *
FROM syscolumns c
INNER JOIN sysobjects o
ON o.Id=c.Id
WHERE o.name = 'JOB'
AND c.name ='JOB_ID'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-03 : 02:09:40

quote:
Originally posted by sunil

When I run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'JOB' AND COLUMN_NAME = 'JOB_ID' , I get "Incorrect syntax near the keyword 'COLUMN'."

Following runs fine and provides information

SELECT *
FROM syscolumns c
INNER JOIN sysobjects o
ON o.Id=c.Id
WHERE o.name = 'JOB'
AND c.name ='JOB_ID'

The catalog view name is COLUMNS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-03 : 02:45:07

quote:
Originally posted by SanjaySutar

Hi,
How to find if a column exists in Database Table ?

thanx in advance

Another method is


if col_length('table_name','column_name') is not null
	print 'column exists'
else
	print 'column doesnt exist'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources