Get Column or parameter properties with COLUMNPROPERTY function - SQL Server

Overview
    COLUMNPROPERTY function returns the property information about the column or parameter. This function returns an integer value. It returns 1 for true, 0 for false, NULL for invalid input/value,  and -1 for XML or large value types for the precision property.
    Catalog Views and Information_schema objects are used to get a lot of information about the column or parameter info in a useful format. But some additional column level properties information is missing which you can get using the COLUMNPROPERTY function.
Catalog Views: sys.columns, sys.procedures, sys.parameters, sys.objects
INFORMATION_SCHEMA: information_schema.columns, information_schema.parameters

Syntax

COLUMNPROPERTY ( id , column , property )

ID	Identifier represents the table object ID or Procedure ID
Column	Column represents the name of the column or parameter
Property	Property represents pre-defined values for the property information

The following are available pre-defined values for property argument.

Value	Description
AllowsNull	Allows null values.
ColumnId	Column ID value corresponding to sys.columns.column_id.
FullTextTypeColumn	The TYPE COLUMN in the table holding the document type information of the column.
GeneratedAlwaysType	Is column value system-generated. Corresponds to sys.columns.generated_always_type
IsColumnSet	Column is a column set. For more information, see Use Column Sets.
IsComputed	Column is a computed column.
IsCursorType	Procedure parameter is of type CURSOR.
IsDeterministic	Column is deterministic. This property applies only to computed columns and view columns.
IsFulltextIndexed	Column is registered for full-text indexing.
IsHidden	Is column value system-generated. Corresponds to sys.columns.is_hidden
IsIdentity	Column uses the IDENTITY property.
IsIdNotForRepl	Column checks for the IDENTITY_INSERT setting.
IsIndexable	Column can be indexed.
IsOutParam	Procedure parameter is an output parameter.
IsPrecise	Column is precise. This property applies only to deterministic columns.
IsRowGuidCol	Column has the uniqueidentifier data type, and is defined with the ROWGUIDCOL property.
IsSparse	Column is a sparse column. For more information, see Use Sparse Columns.
IsSystemVerified	The Database Engine can verify the determinism and precision properties of the column. This property applies only to computed columns and columns of views.
IsXmlIndexable	The XML column can be used in an XML index.
Precision	Data type length of the column or parameter.
Scale	Scale for the column or parameter data type.
StatisticalSemantics	Column is enabled for semantic indexing.
SystemDataAccess	Column is derived from a function that accesses data in the system catalogs or virtual system tables of SQL Server. This property applies only to computed columns and columns of views.
UserDataAccess	Column is derived from a function that accesses data in user tables, including views and temporary tables, stored in the local instance of SQL Server. This property applies only to computed columns and columns of views.
UsesAnsiTrim	ANSI_PADDING was set ON at time of table creation. This property applies only to columns or parameters of type char or varchar.

Examples of COLUMNPROPERTY function

-- Get table and identity column names in adventureworks database
SELECT top 5
    OBJECT_NAME(id) as ObjectName, Name as Identity_Column
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1
ORDER BY 1,2

-- Returns the length of the column (Person.Person.Title)
SELECT COLUMNPROPERTY(OBJECT_ID('Person.Person'), 'Title','PRECISION') AS COL_LEN

Reference :  https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql