Solved

Column metadata

Posted on 2006-11-16
2
949 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 50 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 50 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

687 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