REMARK: ID for a table should be nested out from sysobjects. Simply selecting from syscolums are not recommended. One should make an inner join with sysobjects, syscolumns and systypes, and an additional left join with sysindexkeys and sysindexes if one wants to extract, yes you got it, the indexes too.
You, might also like to be careful about the owner ship (uid), and the object type (type, xtype). Secondly: MS do not recommend one to use/depend on the system tables. SELECT o.Name AS 'Table', c.Name As 'Column', t.Name AS 'Type', i.Name AS 'Index' FROM sysobjects o INNER JOIN syscolumns c on o.id = c.id INNER JOIN systypes t on c.xtype = t.xtype LEFT JOIN sysindexkeys ik on c.colid = ik.colid and ik.id = c.id LEFT JOIN sysindexes i on i.indid = ik.indid and i.id = c.id But as David remarked, it might be better to try to make it platform independent (e.g. one less thing to change if/when you need to move the system...). > -----Original Message----- > From: David Hollister [mailto:[EMAIL PROTECTED]] > Sent: Sunday, August 18, 2002 4:57 PM > To: [EMAIL PROTECTED] > Subject: [PHP-WIN] Re: Getting Field Names in Microsoft SQL > > > The "select * from [tablename]" approach for MS SQL may be problematic if > you have very large tables even if you only return one row. You don't have > an accessible index for "select *". It also gives you no information about > table data types. That's why I suggested using a "select [name] from > syscolumns where id = [your_table_id]". If you are using MS SQL, you've > already made a platform choice (or had it jammed down your throat), and I > don't believe that one select statement determines portability. It all > depends on your requirements. > > Dave > > -----Original Message----- > From: Brad Thomas [mailto:[EMAIL PROTECTED]] > Sent: Friday, August 16, 2002 9:35 PM > To: [EMAIL PROTECTED] > Subject: [PHP-WIN] Re: Getting Field Names in Microsoft SQL > > "R.S. Herhuth" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > > I'm trying to build a dynamic script that basically extracts all of the > > fields in a MS SQL database...I just can't seem to figure out how to get > > at the field names themselves. I am using the mssql_fetch_array > > followed by the $row['field_name'] in an array. But I would like to > > make the field_name dynamic (i.e. not knowing the field_names ahead of > > time) which would make the script adaptable to any table in the > > database. So if I get the field's value by $row['field_name'] how do I > > get at the field's name? > > > > thanks, > > Ron > > > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
