In SQL Server, the COLUMNPROPERTY() function returns column or parameter information.

For example, you can use it to return information about a column in a table, a parameter for a stored procedure, etc

It accepts three arguments: the ID of the table or procedure, the applicable column or parameter, and the property you want information about.

Syntax

The syntax goes like this:

COLUMNPROPERTY ( id , column , property )

Example 1 – Query a Table

In this example, I get information about a column within a table.

USE Music;
SELECT 
  COLUMNPROPERTY(OBJECT_ID('Artists'), 'ArtistId', 'AllowsNull') AS [Result];

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

In this case, the ArtistId column doesn’t allow NULL values.

Let’s switch to a different column:

SELECT 
  COLUMNPROPERTY(OBJECT_ID('Artists'), 'ActiveFrom', 'AllowsNull') AS [ActiveFrom],
  COLUMNPROPERTY(OBJECT_ID('Artists'), 'ActiveFrom', 'Precision') AS [Precision],
  COLUMNPROPERTY(OBJECT_ID('Artists'), 'ActiveFrom', 'Scale') AS [Scale];

Result:

+--------------+-------------+---------+
| ActiveFrom   | Precision   | Scale   |
|--------------+-------------+---------|
| 1            | 10          | 0       |
+--------------+-------------+---------+

In this case, the ActiveFrom column does allow NULL values. It also has a precision of 10 and a scale of 0.

You’ll notice that I use the OBJECT_ID() function to return the ID of the table. Without this function, I’d need to know the ID (or I’d need to do another query just to get the ID).

Here’s what OBJECT_ID() returns in the above example:

SELECT OBJECT_ID('Artists') AS Result;

Result:

+-----------+
| Result    |
|-----------|
| 885578193 |
+-----------+

So now that we know the ID, we could pass that to the COLUMNPROPERTY() function instead:

SELECT 
  COLUMNPROPERTY(885578193, 'ActiveFrom', 'AllowsNull') AS [ActiveFrom],
  COLUMNPROPERTY(885578193, 'ActiveFrom', 'Precision') AS [Precision],
  COLUMNPROPERTY(885578193, 'ActiveFrom', 'Scale') AS [Scale];

Result:

+--------------+-------------+---------+
| ActiveFrom   | Precision   | Scale   |
|--------------+-------------+---------|
| 1            | 10          | 0       |
+--------------+-------------+---------+

Example 2 – Query a Procedure

In this example, I get information about a parameter of a stored procedure.

USE Music;
SELECT 
  COLUMNPROPERTY(
      OBJECT_ID('dbo.uspGetAlbumsByArtist'), 
      '@ArtistId', 
      'IsOutParam') AS [Result];

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

So it’s exactly the same syntax. In this case, the parameter @ArtistId is not an output parameter.

Full List of Options

Here’s a full list of arguments you can pass to COLUMNPROPERTY() at the time of writing:

• AllowsNull
• ColumnId
• FullTextTypeColumn
• GeneratedAlwaysType
• IsColumnSet
• IsComputed
• IsCursorType
• IsDeterministic
• IsFulltextIndexed
• IsHidden
• IsIdentity
• IsIdNotForRepl
• IsIndexable
• IsOutParam
• IsPrecise
• IsRowGuidCol
• IsSparse
• IsSystemVerified
• IsXmlIndexable
• Precision
• Scale
• StatisticalSemantics
• SystemDataAccess
• UserDataAccess
• UsesAnsiTrim

See the Microsoft documentation for a detailed explanation of each property.

Also see OBJECTPROPERTYEX() for a similar function that returns information about schema-scoped objects rather than databases, and DATABASEPROPERTYEX() that returns property info for databases.