Post
Ask Question

Querying SQL Object Properties

Couple of times, we need to dig a bit for table Meta data while working on complex SQL stored procedures or designing complex SQL queries.
 
Below are some query example where you can actually look through column metadata that gives you information regarding whether the column is identity, if it allows null or not and so on.
 
For the example, consider below table with structure as shown in screenshot.
 
 
 
First metadata function to take a look is COLUMNPROPERTY. Basically, it accepts three parameters.
  1. COLUMNPROPERTY (id, column, property)  
Where id is the table id, which you can find by using function. OBJECT_ID (TABLE NAME)
 
Consider below example where I am trying to pull some information about, whether the given column is identity or allows null.
  1. DECLARE @ColAllowNULL BIT;   
  2.    
  3. SELECT @ColAllowNULL= COLUMNPROPERTY(OBJECT_ID('dbo.Employee'),'external_id','AllowsNull')   
  4.    
  5. PRINT 'Column external_id allows Null? : ' + CONVERT(CHAR,@ColAllowNULL)   
  6.    
  7. SELECT @ColAllowNULL= COLUMNPROPERTY(OBJECT_ID('dbo.Employee'),'name','AllowsNull')   
  8.    
  9. PRINT 'Column name allows Null? : ' + CONVERT(CHAR,@ColAllowNULL)   
As mentioned in the definition of the COLUMNPROPERTY, first parameter is the object id of the table, second parameter is column name for which you want to query the property and the third parameter is the name of property. In above example, I am checking if specific column(s) allow(s) null or not.
 
The output: where 0 means, it doesn’t allow the null and 1 means it allows null. You can verify the answer with the design of the table shown.
  1. Column external_id allows Null? : 0   
  2.    
  3. Column name allows Null? : 1   
Another example:
  1. SELECT @ColIndentity= COLUMNPROPERTY (OBJECT_ID('dbo.Employee'),'external_id','IsIdentity')   
  2.    
  3. PRINT 'Column external_id is Indentity? : ' + CONVERT(CHAR,@ColIndentity)   
  4.    
  5. SELECT @ColIndentity= COLUMNPROPERTY(OBJECT_ID('dbo.Employee'),'id','IsIdentity')   
  6.    
  7. PRINT 'Column id is Indentity? : ' + CONVERT(CHAR,@ColIndentity)   
Output

Column external_id is Indentity? : 0 
Column id is Indentity? : 1 
 
That is all about how to read the Meta data about column in terms of how it accepts the data. This query would be useful when you want to check or alter any constraints for the column. It gets rid of querying (joining) multiple tables (in code) which is quite a requirement to write select query to check if constraints exist or not.
 
Please leave your comments if you have a question regarding this.