Select Columns names from a table.

[Faisal]

Hello Friends..

How can we get the column names from a table in Oracle as well as from SQL
Server db.
Is there any sql functions or procedures to do it..
Pls any help...

Thanks in advance..
Faisal

[Bernie]

"Faisal" <fasmohd00@hotmail.com> wrote:
>
>Hello Friends..
>
>How can we get the column names from a table in Oracle as well as from SQL
>Server db.
>Is there any sql functions or procedures to do it..
>Pls any help...
>
>Thanks in advance..
>Faisal
>

Hi

The simpliest way is to use ADOX but you can use ADO as well. Open a Recordset
like SELECT * FROM MyTable WHERE 1=2 (you don't need to get any records for
this) and scroll through the Fields collection. Each Field has allways a
Name property. Type and Precision are other useful properties...

Bernie

[Michael Culley]

For SQL Server:

SELECT dbo.syscolumns.name AS ColName
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id =
dbo.sysobjects.id
WHERE (dbo.sysobjects.name = N'MyTableName')

--
Michael Culley
www.vbdotcom.com


"Bernie" <magnus.bernroth@rejlers.se> wrote in message
news:3c70c3a5$1@10.1.10.29...
>
> "Faisal" <fasmohd00@hotmail.com> wrote:
> >
> >Hello Friends..
> >
> >How can we get the column names from a table in Oracle as well as from
SQL
> >Server db.
> >Is there any sql functions or procedures to do it..
> >Pls any help...
> >
> >Thanks in advance..
> >Faisal
> >
>
> Hi
>
> The simpliest way is to use ADOX but you can use ADO as well. Open a
Recordset
> like SELECT * FROM MyTable WHERE 1=2 (you don't need to get any records
for
> this) and scroll through the Fields collection. Each Field has allways a
> Name property. Type and Precision are other useful properties...
>
> Bernie

[Faisal]

Hei Friends,
Thanks for ur help..
I succeded the same using ADO.
My code is as
-----------------
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim prop As ADODB.Property

Dim strCon As String

strCon = "DSN=" & txtDsn & ";Uid=" & txtUid & ";pwd=" & txtPwd & ";"

On Error GoTo ErrDB
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.Open strCon
rs.Open Trim(txtTable), con, , , adCmdTable
For Each fld In rs.Fields
print fld.Name
Next

rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
loadColumns = True
Exit Function
ErrDB:
If Err.Number = -2147217865 Then
MsgBox "Table doesnot exist", vbCritical + vbOKOnly, "Invalid Table
Name"
loadColumns = False
Exit Function
End If

---------------------
Regards
Faisal

"Michael Culley" <mike@vbdotcom.com> wrote:
>For SQL Server:
>
>SELECT dbo.syscolumns.name AS ColName
>FROM dbo.syscolumns INNER JOIN
> dbo.sysobjects ON dbo.syscolumns.id =
>dbo.sysobjects.id
>WHERE (dbo.sysobjects.name = N'MyTableName')
>
>--
>Michael Culley
>www.vbdotcom.com
>
>
>"Bernie" <magnus.bernroth@rejlers.se> wrote in message
>news:3c70c3a5$1@10.1.10.29...
>>
>> "Faisal" <fasmohd00@hotmail.com> wrote:
>> >
>> >Hello Friends..
>> >
>> >How can we get the column names from a table in Oracle as well as from
>SQL
>> >Server db.
>> >Is there any sql functions or procedures to do it..
>> >Pls any help...
>> >
>> >Thanks in advance..
>> >Faisal
>> >
>>
>> Hi
>>
>> The simpliest way is to use ADOX but you can use ADO as well. Open a
>Recordset
>> like SELECT * FROM MyTable WHERE 1=2 (you don't need to get any records
>for
>> this) and scroll through the Fields collection. Each Field has allways
a
>> Name property. Type and Precision are other useful properties...
>>
>> Bernie
>
>

[Michael Culley]

>> MsgBox "Table doesnot exist", vbCritical + vbOKOnly, "Invalid Table

As a general rule 'or' should be used instead of +

--
Michael Culley
www.vbdotcom.com


"Faisal" <fasmohd00@hotmail.com> wrote in message
news:3c71cf6f$1@10.1.10.29...
>
> Hei Friends,
> Thanks for ur help..
> I succeded the same using ADO.
> My code is as
> -----------------
> Dim con As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim fld As ADODB.Field
> Dim prop As ADODB.Property
>
> Dim strCon As String
>
> strCon = "DSN=" & txtDsn & ";Uid=" & txtUid & ";pwd=" & txtPwd & ";"
>
> On Error GoTo ErrDB
> Set con = New ADODB.Connection
> Set rs = New ADODB.Recordset
> con.Open strCon
> rs.Open Trim(txtTable), con, , , adCmdTable
> For Each fld In rs.Fields
> print fld.Name
> Next
>
> rs.Close
> con.Close
> Set rs = Nothing
> Set con = Nothing
> loadColumns = True
> Exit Function
> ErrDB:
> If Err.Number = -2147217865 Then
> MsgBox "Table doesnot exist", vbCritical + vbOKOnly, "Invalid
Table
> Name"
> loadColumns = False
> Exit Function
> End If
>
> ---------------------
> Regards
> Faisal
>
> "Michael Culley" <mike@vbdotcom.com> wrote:
> >For SQL Server:
> >
> >SELECT dbo.syscolumns.name AS ColName
> >FROM dbo.syscolumns INNER JOIN
> > dbo.sysobjects ON dbo.syscolumns.id =
> >dbo.sysobjects.id
> >WHERE (dbo.sysobjects.name = N'MyTableName')
> >
> >--
> >Michael Culley
> >www.vbdotcom.com
> >
> >
> >"Bernie" <magnus.bernroth@rejlers.se> wrote in message
> >news:3c70c3a5$1@10.1.10.29...
> >>
> >> "Faisal" <fasmohd00@hotmail.com> wrote:
> >> >
> >> >Hello Friends..
> >> >
> >> >How can we get the column names from a table in Oracle as well as from
> >SQL
> >> >Server db.
> >> >Is there any sql functions or procedures to do it..
> >> >Pls any help...
> >> >
> >> >Thanks in advance..
> >> >Faisal
> >> >
> >>
> >> Hi
> >>
> >> The simpliest way is to use ADOX but you can use ADO as well. Open a
> >Recordset
> >> like SELECT * FROM MyTable WHERE 1=2 (you don't need to get any records
> >for
> >> this) and scroll through the Fields collection. Each Field has allways
> a
> >> Name property. Type and Precision are other useful properties...
> >>
> >> Bernie
> >
> >
>