How do I extract Column info for a particular column?

Question

Post reply

How do I extract Column info for a particular column?

billross

SSC Eights!

Points: 922
More actions
June 22, 2008 at 6:36 pm

#189474

I want extract Column info for the first three columns in my table:
Is it a Primary Key?
What is the Data Type?
What is the default value?
I would also like to do this for a column by its name.
Is this possible?
Thanks in advance.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com

Viewing 15 posts - 1 through 15 (of 19 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Try this:

Select C.*, K.Constraint_Name, K.ORDINAL_POSITION as [KEY_POSITION]

From INFORMATION_SCHEMA.COLUMNS C

Left Outer Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE K

ON K.Table_Schema=C.Table_Schema

And K.Table_Name=C.Table_Name

And K.Column_Name=C.Column_Name

And K.Constraint_Name Like 'PK_%'

Order By Table_Schema, Table_Name, Ordinal_Position

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Did below, while rbarryyoung was posting a better answer then I had:

Look in BOL at data available in these system views

sys.allcolumns

sys.objects

sys.columns

sys.tables

and "Querying the SQL Server System Catalog FAQ" (Yes this is in BOL) where you will find sample code. For example:

USE ;

GO

SELECT c.name AS column_name

,c.column_id

,SCHEMA_NAME(t.schema_id) AS type_schema

,t.name AS type_name

,t.is_user_defined

,t.is_assembly_type

,c.max_length

,c.precision

,c.scale

FROM sys.columns AS c

JOIN sys.types AS t ON c.user_type_id=t.user_type_id

WHERE c.object_id = OBJECT_ID(' ')

ORDER BY c.column_id;

GO

Hope this helps

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

billross SSC Eights! Points: 922 More actions · Answer 3

Thanks for those responses.

I notice someone else using the syntax:

SELECT name, xusertype, xtype

FROM syscolumns

WHERE id=@id etc...

but upon closer inspection, that is the old SS2000 syntax, now called the COMPATIBILITY VIEW of the Metadata.

The INFORMATION_SCHEMA approach is called the INFORMATION SCHEMA VIEW of the metadata.

The Sys.Columns approach is called the CATALOG VIEW.

(I find the co-existence of various ways of doing the same thing maddening, especially when I don't realize I'm dealing with apples, oranges and rotting bananas.)

Thanks again,

"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Glad we could help...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

I find the co-existence of various ways of doing the same thing maddening

I find it makes sex interesting :w00t: Oops... sorry... wrong forum 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 6

billross (6/22/2008)
I want extract Column info for the first three columns in my table:
Is it a Primary Key?
What is the Data Type?
What is the default value?
I would also like to do this for a column by its name.
Is this possible?
Thanks in advance.

Type your table name into a query window.. double click to highlight the full table name.. then hit Alt F1.

Try it.. if I'm understanding yuor question it'll show you everything you need to know (defaults are shown in the constraints section).

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 7

I have the Developers Edition for 2005 which is supposedly the same as the Enterprise Edition... the Alt F1 trick doesn't work for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 8

Jeff Moden (6/25/2008)
I have the Developers Edition for 2005 which is supposedly the same as the Enterprise Edition... the Alt F1 trick doesn't work for me.

SQL2000 Query analyser - works

Microsoft SQL Server Management Studio - works

You didn't change the default key bindings did you? I'm using a standard set up..

If all else fails you can invoke the command manually..

sp_help TableName

.. works with functions, sp's, views etc.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 9

No... I've verified that sp_help is bound to Alt-F1... it just doesn't work for me. I highlight the table name as you suggested and press Alt-F1... all that happens is that the QA Window looses focus.

No biggee... I normally don't need that type of thing... was just curious...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 10

Jeff Moden (6/25/2008)
No... I've verified that sp_help is bound to Alt-F1... it just doesn't work for me. I highlight the table name as you suggested and press Alt-F1... all that happens is that the QA Window looses focus.
No biggee... I normally don't need that type of thing... was just curious...

I love using it, comes in handy.. especially when you put the results into a grid. You can then select all the column names and copy/paste into your query. I'm lazy.. what can I say.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 11

Oh yeah... I'm a bit lazy myself... that's why I wrote a little ditty that also adds the commas to the column names... 😀

I've got one for INSERT/SELECTs... writes the whole thing. I should probably post it one day...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

billross SSC Eights! Points: 922 More actions · Answer 12

Alt-F1 works for me in Katmai Developer Edition (SS Ver 10). Looks handy. I am self taught (or rather, self learning) and there are a world of great ideas on this board that I would never have thought existed. This is a wonderful resource.

Someone mentioned the built in stored procedures that underlies many of the nifty tools. I'm thinking it will do me a world of good to study those, both to see what is available pre-built, but also to see how they do it.

Have a great day.

"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 13

Studying the code for sp_help and sp_SpaceUsed will give you a wealth of knowledge about system "tables"... the code sucks (especially for sp_SpaceUsed) but where they get some of the stuff from is good.

Why they didn't make sp_SpaceUsed good for 1 or all tables is beyond me... would have been very easy to do. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 14

Jeff Moden (6/25/2008)
Studying the code for sp_help and sp_SpaceUsed will give you a wealth of knowledge about system "tables"... the code sucks (especially for sp_SpaceUsed) but where they get some of the stuff from is good.
Why they didn't make sp_SpaceUsed good for 1 or all tables is beyond me... would have been very easy to do. 😛

I'd probably do something like..

CREATE TABLE #SizeTable (

Name CHAR (255),

Rows INT,

Reserved varchar(255),

Data varchar(255),

index_size varchar(255),

unused varchar(255)

)

INSERT INTO #SizeTable

EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused "?"'

SELECTName,

Reserved,

Data,

index_size,

unused

FROM#SizeTable

ORDER BY

CAST(REPLACE(Reserved,'KB','') as int) DESC

No idea if it'll work in 2005 though..