IT-Stuffs

To using of 'INFORMATION_SCHEMA.Columns' to get the Sql Objects details as table. The following code snipts used to check the table is having particular columns and get the total no. of available columns in Sql Server.

USE [gat]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UDF_IsColumnAvail]
                                          (
                                                  @TableName  VARCHAR(50),
                                                  @ColumnName VARCHAR(50),
                                                  @Option     SMALLINT
                                          )
RETURNS INT
BEGIN
        DECLARE @RecCnt INT
        IF(@Option=1)
        BEGIN
                SELECT @RecCnt=COUNT(*)
                FROM   [10.16.105.89].[Misrepdb].INFORMATION_SCHEMA.Columns
                WHERE  LTRIM(RTRIM(TABLE_NAME))=LTRIM(RTRIM(@TableName))
        END
        ELSE
        BEGIN
                SELECT @RecCnt=COUNT(*)
                FROM   [10.16.105.89].[Misrepdb].INFORMATION_SCHEMA.Columns
                WHERE  LTRIM(RTRIM(TABLE_NAME)) =LTRIM(RTRIM(@TableName))
                AND    LTRIM(RTRIM(Column_NAME))=LTRIM(RTRIM(@ColumnName))
        END
        RETURN @RecCnt
END
GO

To Call the User Defind Function

SELECT [GAT].[DBO].[UDF_IsColumnAvail]('ViFLTESTWIPFloder_us','Cust',1)
BEGIN TRY
if([GAT].[DBO].[UDF_IsColumnAvail]('ViFLTESTWIPFloder_us','Cust')>0)
 print 'ok'
 else
  print 'failed'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()    AS ErrorNumber   ,
  ERROR_SEVERITY()  AS ErrorSeverity ,
  ERROR_STATE()     AS ErrorState    ,
  ERROR_PROCEDURE() AS ErrorProcedure,
  ERROR_LINE()      AS ErrorLine     ,
  ERROR_MESSAGE()   AS ErrorMessage
END CATCH

Read User's Comments