Getting a list of table names from a database

  • Thread starter Thread starter Wali Akthar
  • Start date Start date
W

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?
 
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
 
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
 
Back
Top