Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Meta Data FunctionsRSS Feeds

COLUMNPROPERTY Function

Posted By: Sarita Patel     Category: SQL Server     Views: 3363

This article explains about COLUMNPROPERTY function in sql server with examples.

COLUMNPROPERTY function is used to get information about column or procedure parameters.


Syntax of COLUMNPROPERTY Function :

COLUMNPROPERTY ( 'id' ,'column','property' )

id is a identification number of table or procedure.

column is a name of the column or procedure parameter.

property is an expression containing the information to be returned for id, and can be any of these values.

Value

Description

Returned Value

AllowsNull

Allows null values.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsComputed

The column is a computed column.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsCursorType

The procedure parameter is of type CURSOR.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsDeterministic

The column is deterministic. This property applies only to computed columns and view columns.

1 = TRUE
0 = FALSE
NULL = Invalid input. Not a computed column or view column.

IsFulltextIndexed

The column has been registered for full-text indexing.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsIdentity

The column uses the IDENTITY property.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsIdNotForRepl

The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR REPLICATION is specified, the IDENTITY_INSERT setting is not checked.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsIndexable

The column can be indexed.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsOutParam

The procedure parameter is an output parameter.

1 = TRUE
0 = FALSE
NULL = Invalid input

IsPrecise

The column is precise. This property applies only to deterministic columns.

1 = TRUE
0 = FALSE
NULL = Invalid input. Not a deterministic column

IsRowGuidCol

The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property.

1 = TRUE
0 = FALSE
NULL = Invalid input

Precision

Precision for the data type of the column or parameter.

The precision of the specified column data type

NULL = Invalid input

Scale

Scale for the data type of the column or parameter.

The scale

NULL = Invalid input

UsesAnsiTrim

ANSI padding setting was ON when the table was initially created.

1= TRUE
0= FALSE
NULL = Invalid input


 Return type of COLUMNPROPERTY function is an integer.




Examples of COLUMNPROPERTY Function :

Example 1 : Use of COLUMNPROPERTY function to get column information in select clause

SELECT COLUMNPROPERTY( OBJECT_ID('Customers'),'ContactName','AllowsNull')

Output
1

Above example returns 1 means ContactName field of table Customers allows null value. 



Example 2 : Use of COLUMNPROPERTY function to get paramter information in select clause

SELECT COLUMNPROPERTY( OBJECT_ID('GET_CUSTOMER_NAMES'),'@my_cursor','IsCursorType')

Output
1

Above example returns 1 means parameter @my_cursor of GET_CUSTOMER_NAMES procedure is of type cursor.
  
Share: 

 
 
 

Didn't find what you were looking for? Find more on COLUMNPROPERTY Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of COLUMNPROPERTY Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!