perticular column exists in a table or not

Posted by Mahendrabasutkar under Sql Server on 6/23/2011 | Points: 10 | Views : 3488 | Status : [Member] | Replies : 10
How to check a perticular column exists in a table or not in sql server 2000




Responses

Posted by: Susanthampy on: 6/23/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
if Exists(select * from sys.columns where Name = N'columnName'  

and Object_ID = Object_ID(N'tableName'))

begin

-- Column Exists

end


Regards,
Susan

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Susanthampy on: 6/23/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100))

RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
SET @Result = 'T'
END
ELSE
BEGIN
SET @Result = 'F'
END
RETURN @Result;
END
GO

GRANT EXECUTE ON [ColumnExists] TO [whoever]
GO


Regards,
Susan

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mahendrabasutkar on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Susan,

The both query you have given is not working for 'else' condition.
I'm working in sql server 2000 environment, will you please verify and revert to me.
Please check even for else condition

Thanks$Regards
mahendrabasutkar

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 6/23/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Check this code

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )


Thanks
Karthik
www.f5Debug.net

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mahendrabasutkar on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Karthik,

The query you have given is not working for 'else' condition.
I'm working in sql server 2000 environment, will you please verify and revert to me.
Please check even for else condition

Thanks$Regards
mahendrabasutkar


Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Susanthampy on: 6/23/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
IF COL_LENGTH('table_name','column_name') IS NULL

BEGIN
/*Column does not exist or caller does not have permission to view the object*/
END


Regards,
Susan

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 6/24/2011 [Member] Starter | Points: 25

Up
0
Down
IF EXISTS(select 1 from sys.columns COLS 

INNER JOIN sys.objects OBJS ON OBJS.object_id=COLS.object_id and OBJS.type='U'
AND OBJS.name='TableName' AND COLS.name='ColumnName')
Print 'Column Found'
Else
Print 'Column Not Found'


Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/24/2011 [Member] Starter | Points: 25

Up
0
Down
try this

If col_length('table_name','column_name') is null


--do something




Thanks&Regards
LakshmiNarayana Nalluri.

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mahendrabasutkar on: 6/27/2011 [Member] Starter | Points: 25

Up
0
Down
Hi LakshmiNarayana,

Else condition is not working for your query.
Please suggest me about it.

Thanks&Regards
mahendrabasutkar

Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 6/27/2011 [Member] Starter | Points: 25

Up
0
Down
IF EXISTS(select 1 from sys.columns COLS 


INNER JOIN sys.objects OBJS ON OBJS.object_id=COLS.object_id and OBJS.type='U'

AND OBJS.name='TableName' AND COLS.name='ColumnName')

Print 'Column Found'

Else

Print 'Column Not Found'


Mahendrabasutkar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response