Datatypes of Columns

Question

Datatypes of Columns

sujosh

Ten Centuries

Points: 1366
More actions
January 29, 2002 at 10:24 am

#78200

Greetings:
In my app I want to get the primarykeys and the datatypes of that column for a table.
Now I am aware of sp_pkeys and sp_columns will do it in proably two steps. Is there a one step process where I can get this by passing a table name I can get the pcolumns and pcolum datatypes?
Or do you think I have to do it myself?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You will have to do it yourself somewhere. sp_help (table) will do it, but will return multiple result sets. You can recieve and process these in ADO.

Steve Jones

steve@dkranch.net

sujosh Ten Centuries Points: 1366 More actions · Answer 2

Hi Steve,

I found out something from syscolumns. There is a column called typestat. when I read in BOL all it says that it is used for internal use only.

But it looks like if it has 1 it is a primary key column. But again it is not true always. I came across a table where it had a 1 in the primary key column and a 1 in the next column.

Could some one please tell me what is this typestat column denotes?

Becasue If I can get the pkey from syscolumns I can do a join to systypes and get the type.

Thanks

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

If you want to use Information Schema Views:

CREATE PROC usp_GetPrimaryKeyCols

@Table sysname

AS

SELECT C.COLUMN_NAME, C.DATA_TYPE

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

AND KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA

AND KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG

JOIN INFORMATION_SCHEMA.COLUMNS C

ON C.COLUMN_NAME = KCU.COLUMN_NAME

AND C.TABLE_NAME = KCU.TABLE_NAME

AND C.TABLE_SCHEMA = KCU.TABLE_SCHEMA

AND C.TABLE_CATALOG = KCU.TABLE_CATALOG

WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

AND TC.TABLE_NAME = @Table

GO

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

sujosh Ten Centuries Points: 1366 More actions · Answer 4

sujosh

Ten Centuries

Points: 1366

January 29, 2002 at 3:24 pm

#422843

That works great. Thanks Brian!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

You're welcome. Information Schema views can come in handy because we can avoid the system tables with them.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

sujosh Ten Centuries Points: 1366 More actions · Answer 6

Hi Kelly,

That works great no doubt about it and thats what I am using. I am little curious to know wont the pkeys information be stored somewhere in the syscolumns though? or somewhere in the systables? Becasue we have all the other (well most of the) info in the systables.

Thanks

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

The information is stored in the system tables, but Microsoft reserves the right to change them at any time. The Information Schema views are mandate by the SQL-92 standard and are less prone to change. That's why I used the Information Schema views for the solution. For a bit more:

http://www.sqlservercentral.com/columnists/bkelley/informationschemaviews.asp

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

sujosh Ten Centuries Points: 1366 More actions · Answer 8

Thanks Brian!! That was a good article for some one like me who hasnt worked with Inforamtion_schemas a whole lot. Very educating.

Thanks