MSDN article
Returns information about a column or a parameter.
COLUMNPROPERTY( id of table or procedure, name of column or parameter, property)

To see some usages, create a table using the script below.
create table Person
(
id int identity(1,1) not null primary key,
FirstName nvarchar(100) not null,
LastName nvarchar(100) not null,
DateOfBirth datetime not null
)


Select COLUMNPROPERTY( Object_id('Person'), 'ID', 'AllowsNull' ) returns 0, because the id column is marked as not null.
Select COLUMNPROPERTY( Object_id('Person'), 'ID', ColumnId' ) returns 1, but this value may change. To verify this, create a table using the script below:


Create table Students2
(
ShortIdn nvarchar(5) not null primary key,
FirstName nvarchar(100) not null,
LastName nvarchar(100) not null,
InternalId int identity(1,1) not null
)

Now check the InternalId column id using COLUMNPROPERTY:

select COLUMNPROPERTY( Object_id('Students2'), 'InternalId', 'ColumnId' )

returns 4 on my system.

A column id does not change if a column is dropped.
Let’s check this. We’ll drop the column LastName from the table Students2 and check again the id of the column InternalId.

ALTER table Students2 drop column LastName


select COLUMNPROPERTY( Object_id('Students2'), 'InternalId', 'ColumnId' )

returns again 4 on my system.

Select COLUMNPROPERTY( Object_id('Person'), 'ID', FullTextTypeColumn' ) returns 0, as this column is not full-text indexed.

Select COLUMNPROPERTY( Object_id('Person'), 'ID', IsComputed' ) returns 0, as this column is not a computed one.

Select COLUMNPROPERTY( Object_id('Person'), 'ID', IsIdentity' ) returns 1, as this column is an identity one.

Advertisements