Retrieving column names from a MSSQL table

rafadc

I'm writing a migration script from MSSQL to mySQL and I want to replicate all data from MSSQL database to a mySQL database.

I've read all tableas in MSSQL database but the problem arrives as I need to read all fields in each table.

If I try to do "sp_help tablename" to get all fields I get 3 resultsets wich I can't read from PHP. At least I don't know how. Anyway the query returns FALSE as if it was an error.

If I try to do "sp_columns @table_name = '$tableName'" also the query returns as if it was an error.

Before I send this queries I do an echo and copy the values from the queries to SQL Query Analyzer and both queries work perfectly.

Anyone knows a better way to do this or a way to solve my problem?

Tnanks in advance.

Roger_Ramjet

Well, I don't use mssql often enough to carry any of the details in my head so I can't help you with any of that t-sql stuff. But it's not neccessary anyway. Simple way to get field information is to use mssql_fetch_field

// query all fields from the table in question, I guess it's something like this for mssql
$sql = "SELECT * FROM table TOP 1";
$msres = mssql_query($sql);
while ($fld_obj = mssql_fetch_field($msres)) {
   $name[] = $fld_obj->name;
   $col_size[] = $fld->max_length;
   $type[] = $fld_obj->type;
}

There is better code in the user notes for fetch field that you will probably want to use, I just did this for practice.

rafadc

But the problem with this is that I need also to retrieve the data type of the fields so I can reproduce the same structure in another database. With fetch_field i can only get column names and if field is numeric but I can't get if a field is binary or blob.

Thanks anyway. I'll try to investigate more this way.

Roger_Ramjet

Well, as I said I don't use mssql much, and never on web so I don't know the in and outs of it, but it does say that it returns column type $fld->type as well as if it is numeric $fld->numeric so I assumed that it would differentiate numeric types. There is some code in the user notes that sez it will return all the fleid details through a query on systables so you might want to read and try that.