Retrieve database structure through ADO/ODBC

[winstonep]

Hi all,
I'm sure I've done this in the past but we are talking at least 2 1/2 years ago now. I'm in desperate need of a way to retrieve the structure of a database table specifically at the moment from SQL Server. I need to be able to use ODBC at the moment unless ADO can use existing ODBC drivers.

Can anyone post some sample code or a reference to how Delphi 6 can be used to retrieve a database structure?

 

[StevenK]

One way is to make use of the [syscolumns] table as follows :

SELECT
(CAST([Name] AS CHAR)) ColName,
(CAST([ColOrder] AS INT)) ColOrder
FROM [syscolumns]
WHERE ID = OBJECT_ID(N'[dbo].[TABLE1]')
ORDER BY [ColOrder]

I think there's a neater (more preferred way) of doing this using a stored procedure but it escapes me at this moment in time.

Steve

 

[LucieLastic]

hi

I don't have access to SQLServer now but I do it in Sybase. I use 3 tables, one as Steven says (syscolumns), another for the column types (systypes) and sysobjects for the table information - this query gives all the tables, their column names and column datatypes.


select s.name, c.name as Fieldname, t.name as Type, c.length as Length
from sysobjects s
inner join syscolumns c on s.id = c.id
inner join systypes t on c.usertype = t.usertype
where s.type='U'
order by s.name, c.colid

Check the table and fieldnames in SQLServer.

lou

 

[LucieLastic]

Steve

..just had a thought, what about

Select * From INFORMATION_SCHEMA.Columns
where Table_name = 'approriateName'

Wouldn't that work?

lou

 

[StevenK]

That does seem to work a treat.
Not seen that one myself - may be making use of that too in the near future.
Thanks

 

[winstonep]

I'll try that second one when I get a chance.

It looks neat and should do exactly what I want. Thanks lou.

Paul Winstone

http://www.logicacmg.com

paul.winstone@logicacmg.com

 

[LucieLastic]

Just one more thing...you may want to use 'like' in the 'where' clause instead of '='.

lou