user tables and data types

Question

Post reply

user tables and data types

SQLSeTTeR

SSCertifiable

Points: 5481
More actions
October 20, 2008 at 5:15 pm

#199819

Can someone help me find the data types for all the user tables in the master database? I know I can find the user tables in the sys.tables but where can I find the data types for those tables? I want to create an excel sheet with tables and their data types, any help is much appreciated.

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

SQL ORACLE

One Orange Chip

Points: 27807

October 20, 2008 at 6:14 pm

#887376

Did you check syscolumns table?

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 2

Yes. I can see the max_length, but not data_types? Any suggestions?

Lowell SSC Guru Points: 323518 More actions · Answer 3

the recommended way is to use the system views like this:

select * from INFORMATION_SCHEMA.COLUMNS

if you want to start doing it yourself, this will get you started:

select object_name(id) as TableName,

name as ColumnName,

type_name(xtype)

from syscolumns

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

tjm (10/20/2008)
Yes. I can see the max_length, but not data_types? Any suggestions?

SELECT * FROM Information_Schema.Columns

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

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 5

That was the table I was looking for. Thanks for all your help...

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 6

You're welcome and thanks for the feedback. Just to be clear, it's a view... not a table. 🙂

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

sayfrend SSCommitted Points: 1733 More actions · Answer 7

I use sp_help table_name

gives me all details of tables, including column names and their data types and data lengths ( if any ).

Thanks

Imran Mohammed.

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 8

Information is great from all of you. Thanks again for reaching out!