Getting a list of table names from a database

[Wali Akthar]

Hi,

Is it possible to get a list of tables in a database in SQL Server?
Is is also possible to get a list of fields in each table?

Are there any examples of this please?

 

[Jem]

If you're not worried about portable code try running the following queries
(I suggest first in SQL Query Analyzer):

select * from information_schema.tables;
select * from information_schema.columns;

These work a treat for System.Data.SqlClient, but if you're using
System.Data.OleDb you can also use the method GetOleDbSchemaTable() of
OleDbConnection.

Jem

 

[Michael Lang]

The following gets just the table names...

SELECT sysobjects.name AS TableName FROM sysobjects
WHERE
(sysobjects.xtype = 'U')
AND
(sysobjects.name <> N'dtproperties')

The following gets the fields for a specific table...

SELECT sysobjects.name AS TableName, ");
syscolumns.name AS ColumnName,
syscolumns.colid AS ColumnSequence,
syscolumns.xtype AS DataType
FROM
sysobjects
INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
WHERE
(sysobjects.name = 'table')
AND
(sysobjects.xtype = 'U')
AND
(sysobjects.name <> N'dtproperties')
ORDER BY
sysobjects.name, syscolumns.colid

Mike