Solved

Column metadata

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now