How to get identity, computed properties of a column in a table

Problem: How to get identity, computed properties of a column in a table?

Solution: COLUMNPROPERTY, starting from SQL server 2008 or higher.

Syntax: COLUMNPROPERTY (id , column , property )   

(Needs input) ID – OBJECT_ID of a table or a procedure.

Column – Column name or parameter name

(Needs Input) Property – Type of information you want to retrieve from the column.

This is a metadata function which spits out an output based on the property you want to read for a specific column or parameter in a procedure.

Example:

Example 1: In this example I would like to see all the information of identity columns in a database.

SELECT c.NAME as IdentityColumn,

                OBJECT_NAME(c.OBJECT_ID) AS tablename

FROM sys.columns c

INNER JOIN sys.tables t

                ON t.object_id = c.object_id

WHERE COLUMNPROPERTY(c.OBJECT_ID, c.NAME, 'IsIdentity') = 1

                AND t.is_ms_shipped = 0

OUTPUT:

IdentityColumn	tablename
EmployeeID	Employees
AuditCallID	AuditCall
CallId	Request
AddressPKey	Address
SubjectId	Subject

Example 2:

In this article I am going to show you all the columns which are full text indexed.

               

SELECT c.NAME as FullTextIndexedColumn,

                OBJECT_NAME(c.OBJECT_ID) AS tablename

FROM sys.columns c

INNER JOIN sys.tables t

                ON t.object_id = c.object_id

WHERE COLUMNPROPERTY(c.OBJECT_ID, c.NAME, 'IsFulltextIndexed') = 1

                AND t.is_ms_shipped = 0

OUTPUT:

FullText IndexedColumn	tablename
AddressLine1	Address
SubjectDescription	Subject