?
Solved

Column metadata

Posted on 2006-11-16
2
Medium Priority
?
955 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:Kan64
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
btutt earned 200 total points
ID: 17958405
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
 
LVL 23

Assisted Solution

by:Christopher Kile
Christopher Kile earned 200 total points
ID: 17958557
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question