Column metadata

I want to extract column metadata such as column data type, MaxSize,IsPrimaryKey, IsForeignKey.

I tried this but getting null instead of true/false

use Northwind
select COLUMNPROPERTY(OBJECT_ID('Customers'), 'CustomerID', 'IsPrimaryKey')

thanks
Kan64Asked:
Who is Participating?
 
btuttConnect With a Mentor Commented:
You are looking for OBJECTPROPERTY to use, not COLUMNPROPERTY. i.e.:

select OBJECTPROPERTY(OBJECT_ID('PK_Customers'), 'IsPrimaryKey') will return 1 within the Northwind database.
0
 
Christopher KileConnect With a Mentor Commented:
I suggest that 'IsPrimaryKey' is your problem.  It does not seem to be listed in the property arguments to COLUMNPROPERTY.  To see what a valid return would look like, try SELECT COLUMNPROPERTY(OBJECT_ID('<some table name>'), '<some column name>', 'AllowsNull').  I've tried it and get returns of 0 for false and 1 for true.

Now, OBJECTPROPERTY supports 'IsPrimaryKey', but has only two parameters, the object ID and the property.  In this case, the object ID of the CONSTRAINT that defines the primary key returns true (1).  Check this by going into Query Analyzer/User Tables/<some table with a primary key>/Constraints and taking the primary key constraint (most likely the one beginning with PK_, but if such doesn't exist you can find it by generating the create scripts for the constraint and examining the constraint for the PRIMARY KEY modifier).

Asking if a column is a primary key will always return null, I believe, based on these findings.  In fact, I haven't been able to generate a SQL query that can identify the columns in a primary key up to this point, the data is not easily found.  Is this a requirement of your effort?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.