Board index » delphi » How to retrieve table's column names (MSSQL)

How to retrieve table's column names (MSSQL)

Dear all,

I used to get table's column names by this in MSSQL7:
"select column_name from information_schema.colum where
table_name='MyTableName'"

However, I have an error now, when I execute this command in SQL Explorer
 shipped with Delphi ), I do not get the datafield, it appears as an empty
column with empty data records.

(But if I execute this statement in SQL Query Analyser, I can get the
correct column and records)

Any help is greatly appreciated.

Thanks
John

 

Re:How to retrieve table's column names (MSSQL)


hi John

You can get the field names using the a ADOConnection

YourADOConnection.GetFieldNames(const TableName: String; List: TStrings);

the tables with YourADOConnection.GetTableNames(List: TStrings;
SystemTables: Boolean = False);

HTH Peter

John <state...@pacific.net.sg> schreef in berichtnieuws 399b8d40_2@dnews...

Quote
> Dear all,

> I used to get table's column names by this in MSSQL7:
> "select column_name from information_schema.colum where
> table_name='MyTableName'"

> However, I have an error now, when I execute this command in SQL Explorer
>  shipped with Delphi ), I do not get the datafield, it appears as an empty
> column with empty data records.

> (But if I execute this statement in SQL Query Analyser, I can get the
> correct column and records)

> Any help is greatly appreciated.

> Thanks
> John

Re:How to retrieve table's column names (MSSQL)


Hi Peter,

I have tried the ADO, that works great! thanks.

Just wonder if it can be done by BDE, because we are writting applications
which runs on both MSSQL and Oracle, in Oracle we do this "select
column_name from user_tab_columns", and if we can do "select column_name
from Information_Schema.columns" for MSSQL, we will have a neater program.

It used to be working, just do not know what happened now. Would appreciate
if you could try to execute "select table_name, column_name from
information_schema.column" in SQL Explorer against a SQL database, you will
get the funny "one-empty-column" result, you will understand our
frastration.

Thanks
John

Quote
"peter" <pe...@IT2.NL> wrote in message news:399ba268_2@dnews...
> hi John

> You can get the field names using the a ADOConnection

> YourADOConnection.GetFieldNames(const TableName: String; List: TStrings);

> the tables with YourADOConnection.GetTableNames(List: TStrings;
> SystemTables: Boolean = False);

> HTH Peter

> John <state...@pacific.net.sg> schreef in berichtnieuws
399b8d40_2@dnews...
> > Dear all,

> > I used to get table's column names by this in MSSQL7:
> > "select column_name from information_schema.colum where
> > table_name='MyTableName'"

> > However, I have an error now, when I execute this command in SQL
Explorer
> >  shipped with Delphi ), I do not get the datafield, it appears as an
empty
> > column with empty data records.

> > (But if I execute this statement in SQL Query Analyser, I can get the
> > correct column and records)

> > Any help is greatly appreciated.

> > Thanks
> > John

Re:How to retrieve table's column names (MSSQL)


Hi John

you maybe can try in a query

USE YourDatabaseName
EXEC sp_columns @table_name = 'YourTableName'

This will return all the information

see Query analyzer with the view to Results grid

HTH

Peter

John <state...@pacific.net.sg> schreef in berichtnieuws 399bb637_1@dnews...

Quote
> Hi Peter,

> I have tried the ADO, that works great! thanks.

> Just wonder if it can be done by BDE, because we are writting applications
> which runs on both MSSQL and Oracle, in Oracle we do this "select
> column_name from user_tab_columns", and if we can do "select column_name
> from Information_Schema.columns" for MSSQL, we will have a neater program.

> It used to be working, just do not know what happened now. Would
appreciate
> if you could try to execute "select table_name, column_name from
> information_schema.column" in SQL Explorer against a SQL database, you
will
> get the funny "one-empty-column" result, you will understand our
> frastration.

> Thanks
> John

> "peter" <pe...@IT2.NL> wrote in message news:399ba268_2@dnews...
> > hi John

> > You can get the field names using the a ADOConnection

> > YourADOConnection.GetFieldNames(const TableName: String; List:
TStrings);

> > the tables with YourADOConnection.GetTableNames(List: TStrings;
> > SystemTables: Boolean = False);

> > HTH Peter

> > John <state...@pacific.net.sg> schreef in berichtnieuws
> 399b8d40_2@dnews...
> > > Dear all,

> > > I used to get table's column names by this in MSSQL7:
> > > "select column_name from information_schema.colum where
> > > table_name='MyTableName'"

> > > However, I have an error now, when I execute this command in SQL
> Explorer
> > >  shipped with Delphi ), I do not get the datafield, it appears as an
> empty
> > > column with empty data records.

> > > (But if I execute this statement in SQL Query Analyser, I can get the
> > > correct column and records)

> > > Any help is greatly appreciated.

> > > Thanks
> > > John

Re:How to retrieve table's column names (MSSQL)


Try:

SELECT A.* FROM SYSCOLUMNS AS A, SYSOBJECTS AS B
WHERE
 A.ID = B.ID AND
 B.NAME = 'MyTableName'

Hope this helps.

Mio Nino P. Marquez

Re:How to retrieve table's column names (MSSQL)


Please try to create a view in your database, then select data from the
view.
Quote
"John" <state...@pacific.net.sg> wrote in message news:399b8d40_2@dnews...
> Dear all,

> I used to get table's column names by this in MSSQL7:
> "select column_name from information_schema.colum where
> table_name='MyTableName'"

> However, I have an error now, when I execute this command in SQL Explorer
>  shipped with Delphi ), I do not get the datafield, it appears as an empty
> column with empty data records.

> (But if I execute this statement in SQL Query Analyser, I can get the
> correct column and records)

> Any help is greatly appreciated.

> Thanks
> John

Other Threads