Displaying all tables and their fields.....

[Frankarello]

Im pretty sure this is an easy question..I cant remember though. I would like to create a query (or use a system procedure if one exists) that displays all of the tables and fields in a database. I think I had used a built in procedure before, but cant recall how to do it. Thanks.

 

[Deeba]

sp_tables will return all tables and objects that could be used in a FROM clause.

Hope this helps
~Deeba~

 

[Frankarello]

I really need something with the output as follows:

Field Table datatype datalength

clientid clients nvarchar 50

 

[tlbroadbent]

Try this.

Select
[Field]=c.column_name,

=c.table_name,
datatype=c.data_type,
datalength=
Case When charindex('char', c.date_type)>0
Then c.CHARACTER_MAXIMUM_LENGTH
Else c.NUMERIC_PRECISION End
From information_schema.columns c
Join sysobjects o9
on c.table_name=o.name
Where o.type='U'
Order By c.table_name, c.ordinal_position Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cfFran]

Couple minor changes and it flies...

Select [Field]=c.column_name,

=c.table_name,
datatype=c.data_type,
datalength=
Case When charindex('char', c.data_type)>0
Then c.CHARACTER_MAXIMUM_LENGTH
Else c.NUMERIC_PRECISION End
From information_schema.columns c
Join sysobjects o
on c.table_name=o.name
Where o.type='U'
Order By c.table_name, c.ordinal_position