SQL server schema

[John Sutor]

Does anyone know what objects to use to query a SQL database to get back
all of the tables and then be able to get information on the tables and
column information (name, datatype, max length etc...)


*** Sent via Developersdex http://www.developersdex.com ***

[Bill B]

Re: SQL server schema

Use the System.Data.Sql Client.SqlConne ction class to open a connection
to the data source and then call GetSchema() on the connection to
iterate through the schema definitons.

http://msdn2.microsoft.com/en-us/library/ms254934.aspx

Bill

[JTC ^..^]

Re: SQL server schema

John Sutor wrote:

Does anyone know what objects to use to query a SQL database to get back
all of the tables and then be able to get information on the tables and
column information (name, datatype, max length etc...)
>
>
*** Sent via Developersdex http://www.developersdex.com ***

Each database contains a sysobjects and syscolumns system tables. With a
joins on a few other system tables you can get the information direct
from the database. E.g.

USE your_database
GO
SELECT tbl.name, col.name, typ.name, col.length, col.isnullable,
col.collation
FROM sysobjects tbl
INNER JOIN syscolumns col
ON tbl.id = col.id
INNER JOIN systypes typ
ON col.xtype = typ.xtype
WHERE tbl.xtype = 'u' and typ.name != 'sysname'
ORDER BY tbl.name, col.colorder

--
Regards
JTC ^..^