get all fields in all table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get all fields in all table

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-08 : 23:17:10

hi!

how can i get the list of tables together with their fields with datatype?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 23:36:15

get it from INFORMATION_SCHEMA.COLUMNS

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-09 : 02:55:26

select distinct object_name(c.object_id),c.name from sys.columns c
inner join sys.tables t on t.object_id = c.object_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:01:40

bklr, where is the information about datatypes as requeste per OP?

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-09 : 03:13:51

SELECT DISTINCT OBJECT_NAME(c.OBJECT_ID) tablename,c.name,ty.name 'datatype'
from sys.columns c
INNER JOIN sys.tables t ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:19:16

I prefer the approach suggested by khtan earlier.
It also gives me the schema name together with other useful information.

select * from INFORMATION_SCHEMA.COLUMNS

In the future, direct access to system tables may not be allowed, so beginning to learn and use the INFORMATION_SCHEMEs will be good for you.

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-09 : 03:25:21

Thanks for ur suggesstion peso

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-26 : 04:51:40

[code]SELECT DISTINCT OBJECT_NAME(c.OBJECT_ID) tablename,c.name,ty.name 'datatype'
from sys.columns c
INNER JOIN sys.tables t ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id[/code]

this helps but i want every table these a corresponding select that only shows the fields per table..

example:

Table1
-----------
list of fields here and their datatypes

Table 2
-----------
list of fields here and their datatypes

and so on....

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-06-26 : 04:52:20

INFORMATION_SCHEMA.COLUMNS

this helps too..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources