Solved

Column metadata

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trouble connecting to SqlServer database 4 60
Sql Join Problem 2 43
constraint check 2 40
What's wrong with this T-SQL Foreign Key? 7 42
This article describes some very basic things about SQL Server filegroups.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

774 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