Table and Field listing

[DrewConn]

I am looking for the easiest way to get a listing of all of the tables and fields within a databaes.

Is there a sp or something that will do this?

Thanks.

 

[RileyCat]

sp_help will list the tables in the d.b.

sp_help <tablename> will list the columns in a table.

Or, if you're using MS SQL Analyzer, press F8 and select the D.B./table you're querying about.

 

[JohnDTampaBay]

If you are working with SQL2K, check out the INFORMATION_SCHEMA views, specifically INFORMATION_SCHEMA.Columns and INFORMATION_SCHEMA.Tables.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[dmcmunn]

This query has proven to be a useful tool for documenting an unfamiliar database, in a way that may be manipulated via your spreadsheet of choice...

select o.xtype as object_type
, case o.xtype
when 'U' then 'User Table'
when 'V' then 'User View'
when 'S' then 'System Table'
else 'other'
end as object_type_desc
, o.name as table_view_name
, c.name as column_name
, c.xtype as column_type
, ct.name as column_type_name
, c.length as column_length
, c.xprec as column_precision
, c.xscale as column_scale
, case when c.isnullable = 1 then 'Nulls Allowed' else 'Non-Null' end
from sysobjects o
inner join syscolumns c
on o.id = c.id
inner join systypes ct
on c.xtype = ct.xtype
where o.xtype in ('u','v')
order by o.name, c.colid