SQL Server: Find the Number of Columns in a Table

The following function can be used to find the number of columns in a specified table. The function returns an INT value, so it is a scalar-valued function.

Create the Function

-- Function for finding the number of columns in a given table

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');

The above statement returns the number of columns in the specified table.

Here, `dbo` represents the database schema under which the function is created.

INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view provides information about the columns in tables and views in the database. It is part of the INFORMATION_SCHEMA views provided by SQL Server and follows the SQL standard for metadata access.

The `ORDINAL_POSITION` column indicates the position of each column within the table. The function uses the maximum `ORDINAL_POSITION` value to determine the number of columns.

Note: This example uses `INFORMATION_SCHEMA.COLUMNS` for metadata access. When working specifically with SQL Server, the `sys.columns` catalog view is another commonly used option.