finding table information 1

[ghobbes98]

I was wondering if anybody knows how if given the address of a database to then send an sql query to return the names of the tables in the database. Following on from that how to get the names of the fields in the tables
Thanks for any suggestions
G

 

[mfairchild]

This is for SQL Server 7.0, but should get you started.

SELECT so.name as Table_Name, sc.name as Col_Name, st.name as DataType, sc.prec, sc.scale, sc.isnullable
FROM sysobjects so, syscolumns sc, systypes st
WHERE so.type = 'u'
and so.id = sc.id
and sc.usertype = st.usertype
ORDER BY so.Name, sc.colorder

 

[tlbroadbent]

Another option is to use the Information_Schema views. These have been available since SQL 7.

List tables in a database.
Select *
From dbname.Information_schema.tables

List columns in a table
Select *
From dbname.Information_schema.columns
Where table_name = 'tablename' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.