Look up a table fields, data type, etc.

Question

Look up a table fields, data type, etc.

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
December 4, 2008 at 2:09 pm

#383513

Is there some kind of simple syntax that I can run in order to see what fields, their names, Nullable or not, Data type and Max lenth?
I'd find it very useful sometimes instead of looking at the table properties.
thx,
John

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

sp_help 'tbl'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

This will list all tables and all columns in each table (And the gods of T-SQL preserve me - it uses a cursor - not the best practice, but I wrote this one a long long time ago)

CREATE PROCEDURE dbo.UDP_Table_Definitions

AS

Declare @default VARCHAR(128)

Declare @tname VARCHAR(128)

Declare @cname VARCHAR(128)

Declare @dtype VARCHAR(30)

Declare @dlength INT

Declare @sstatus CHAR(3)

Declare @xcomp CHAR(3)

Declare @xdef INT

SET NOCOUNT ON

Create Table #UDT_TDefs

(

TName VARCHAR(128),

CName VARCHAR(128),

DType VARCHAR(30),

Dlength INT,

Sstatus CHAR(3),

xComp CHAR(3),

xDef VarChar(30)

)

DECLARE table_cursor CURSOR fast_forward FOR

SELECT so.name, sc.name, st.name, sc.length, sc.Status, sc.iscomputed, sc.cdefault

From Sysobjects so, syscolumns sc, systypes st

where so.xtype = 'U' and sc.id = so.id and sc.xtype = st.xusertype AND NOT so.name = 'dtproperties'

Open Table_Cursor

Fetch Next from Table_Cursor into @tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @xdef

While @@Fetch_Status = 0

Begin

Set @Default = ' '

If @xdef > 0

Set @Default = (Select text from syscomments where @xdef = id)

Insert Into #UDT_TDefs

(TName, CName, DType, Dlength, Sstatus, xComp, xDef)

Values (@tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @Default )

Fetch Next from Table_Cursor into @tname, @cname, @dtype, @dlength, @sstatus, @xcomp, @xdef

End

Close Table_Cursor

Deallocate Table_Cursor

Select

tname As 'Table Name', cname as 'Column Name', DType as 'Data Type', dlength as 'Len', sStatus as 'NP1', xComp as 'NP2', xdef as 'Default',

Case tname When 'int' then Cast(dlength as Char(6)) Else ' ' end As 'Precision',

Case sStatus When 8 then 'Yes' When 24 then 'Yes' When 56 then 'Yes' Else ' ' end As 'Allow Nulls',

Case sStatus When 128 then 'Yes' Else ' ' end As 'Identity',

Case xcomp When 1 then 'Yes' Else ' ' end As 'Computed'

From #UDT_TDefs

Order by tname

Drop table #UDT_TDefs

It also uses: Sysobjects, syscolumns, systypes which are being depreciated so for use beyond 2005 switch to the new system views per BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/44fdc387-67b0-4139-8bf5-ed26cf640cd1.htm

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]

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 3

latingntlman

SSCarpal Tunnel

Points: 4348

December 4, 2008 at 3:23 pm

#907978

I'll try both, thx.

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 4

bitbucket (12/4/2008)
And the gods of T-SQL preserve me

Pickle juice or formaldehyde? With or without pork chops? 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Select * From INFORMATION_SCHEMA.COLUMNS

[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 6

Jeff

Pickle juice or formaldehyde? With or without pork chops?

Worked on that SP long before I hand the pleasure and enlightenment of reading your articles, and learning from them. Just did not have the time to redo it since it was written for SQL 2000. I stand chastised.

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]

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

bitbucket (12/5/2008)
Jeff
Pickle juice or formaldehyde? With or without pork chops?
Worked on that SP long before I hand the pleasure and enlightenment of reading your articles, and learning from them. Just did not have the time to redo it since it was written for SQL 2000. I stand chastised.

Heh... oh, no problem, ol' friend... Your post just struck me as a bit ironic and I was just having a bit of fun. 😀 You were rough enough on yourself than for me to pick on you. Besides, we all started somewhere... I remember how I used to code SQL when I first started... one huge query for each batch project... it was actually worse than any cursor anyone will ever write. :hehe:

--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)

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Jeff Moden (12/6/2008)
I remember how I used to code SQL when I first started... one huge query for each batch project... it was actually worse than any cursor anyone will ever write. :hehe:

Can you clarify this a little bit? You're making me worry about my 600-1000+ line queries :hehe:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]