perticular column exists in a table or not

Posted by Mahendrabasutkar under Sql Server on 6/23/2011 | Points: 10 | Views : 4710 | Status : [Member] | Replies : 10

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How to check a perticular column exists in a table or not in sql server 2000

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

0	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

0	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

0	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

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

0	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

0	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

0	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

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

Latest Posts