SQL: Function for finding Number of Column in a Given Table

Below a function which can be used to find the no.of column in a specified Table.This function returns an INT(Scalar Value).This Type of function is known as scalar valued function.


--FUNCTION FOR FINDING NO OF COLUMN FROM GIVEN TABLE

--Create a function with Table_Name as Parameter
CREATE FUNCTION FUN_COL_COUNT(@T_NAME VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @CNT INT
SELECT @CNT=MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@T_NAME
RETURN @CNT
END

--Run the Function

SELECT DBO.FUN_COL_COUNT('Table_name')

This above statement return the no.of column in the Table_name .Here DBO represents DataBase Object(Schema).More about Schema read some other articles/Posts.


Here Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

Posted By: javadevelopersguide
Link:-http://javadevelopersguide.blogspot.com