mssql_fetch_batch
For really detailed table information, use syscolumns, like this:
SELECT c.name, c.prec, c.scale, t.name type
FROM syscolumns c, systypes t, sysobjects o
WHERE o.name = 'yourtablename' AND o.id = c.id AND c.xtype = t.xtype
For other properties see the MS SQL online help. Search for 'syscolumns'.
Or an another solution:
sp_columns @table_name = 'yourtablename', @column_name = 'thecolumnname'
//no "select ..." !!!!
This gives info about only the specified column.
mssql_fetch_field
(PHP 4, PHP 5, PECL odbtp >= 1.1.1)
mssql_fetch_field — Liefert Informationen über ein Feld
Beschreibung
object mssql_fetch_field
( resource
$result
[, int $field_offset = -1
] )mssql_fetch_field() wird verwendet, um im Ergebnis einer Anfrage Informationen über Felder zu erhalten.
Parameter-Liste
-
result -
Der Bezeichner des Ergebnisses, das ausgewertet wird. Dieses Ergebnis stammt von einem Aufruf von mssql_query().
-
field_offset -
Der numerische Feld-Offset. Wurde kein Feld-Offset angegeben, liefert diese Funktion das nächste Feld, das noch nicht abgefragt wurde. Der
field_offsetbeginnt bei Null.
Rückgabewerte
Gibt ein Objekt mit Informationen über ein Feld zurück.
Die Eigenschaften dieses Objekts sind:
- name - Feldname. Wenn das Feld das Ergebnis einer Funktion ist, wird diese Eigenschaft auf computed#N gesetzt, wobei #N eine fortlaufende Nummer ist.
- column_source - die Tabelle, zu der das Feld gehört
- max_length - maximale Feldbreite
- numeric - 1, wenn es sich um ein numerisches Feld handelt
- type - der Feldtyp.
Beispiele
Beispiel #1 mssql_fetch_field()-Beispiel
<?php
// Mit MSSQL verbinden und Datenbank auswählen
mssql_connect('MANGO\SQLEXPRESS', 'sa', 'phpfi');
mssql_select_db('php');
// Eine Select-Anfrage an MSSQL senden
$anfrage = mssql_query('SELECT * FROM [php].[dbo].[persons]');
// Eine Tabelle bauen
echo '<h3>Tabellenstruktur für \'persons\'</h3>';
echo '<table border="1">';
// Tabellenkopf
echo '<thead>';
echo '<tr>';
echo '<td>Feldname</td>';
echo '<td>Datentyp</td>';
echo '<td>Max. Länge</td>';
echo '</tr>';
echo '</thead>';
// Alle Felder anzeigen
echo '<tbody>';
for ($i = 0; $i < mssql_num_fields($anfrage); ++$i) {
// Feldinformationen abrufen
$feld = mssql_fetch_field($anfrage, $i);
// Eine Zeile ausgeben
echo '<tr>';
echo '<td>' . $feld->name . '</td>';
echo '<td>' . strtoupper($feld->type) . '</td>';
echo '<td>' . $feld->max_length . '</td>';
echo '</tr>';
}
echo '</tbody>';
echo '</table>';
// Den Ergebnisspeicher freigeben
mssql_free_result($anfrage);
?>
add a note
User Contributed Notesmssql_fetch_field
huszti_dot_roland_at_freemail_dot_com
11-May-2006 02:51
11-May-2006 02:51
php_rindern_de
25-Oct-2004 10:36
25-Oct-2004 10:36
commenting Reynard Hilman:
for me it looks like the colstat field value of 1 in syscolumns table indicates an Identity Column.
Reynard Hilman
23-Oct-2003 04:40
23-Oct-2003 04:40
If you want to describe table structure (like mysql 'desc table' command), sending this query might help:
<?
$sql = "SELECT c.name, c.isnullable, c.length, c.colstat, t.name type
FROM syscolumns c, systypes t, sysobjects o
WHERE o.name = '$table' AND o.id = c.id AND c.xtype = t.xtype";
?>
I suspect the colstat field in syscolumns table indicates primary key when its value is 1
bmaddy_at_class_dot_umn_dot_edu
30-Apr-2003 04:41
30-Apr-2003 04:41
Be aware that this function will only return the first 30 characters of the name of the column. If the actual column name is longer, it will be truncated. This is at least true with the following setup:
PHP 4.3.1
MSSQL 8.00.760
Have a good day everyone!
Brian
alonf at spiralsolutions dot com
28-Nov-2002 11:47
28-Nov-2002 11:47
As kubalaa at bigfoot dot com note mssql_fetch_field->column_source return field name instead table name also with MSSQL2000 connection. Be adwised!!!
pong at taft dot org
16-Mar-2002 02:07
16-Mar-2002 02:07
When you mssql_fetch_field(int result), you need to do loop to get the name of each field. Something like:
while($fld = mssql_fetch_field($rs)){
echo $fld->name . "<br>";
}
I am wondering why we cannot refer it by a field number.
mdean at kcnet dot com
02-Jul-2001 12:33
02-Jul-2001 12:33
A quicker query to retrieve table names from the database:
select name from sysobjects where type='u'
skipsey at hotmail dot com
08-Feb-2001 04:58
08-Feb-2001 04:58
It seems fairly hard to get a list of the tables from your database using MSSQL but this seems to do the trick. This is set to get only the User Tables and ignores the sytem tables.
function GetField($res,$field,$number) {
return stripSlashes(mssql_result($res,$number,"$field"));
}
mssql_connect("server","","") or die ("help me!");
mssql_select_db("") or die ("Noooo!");
$result = mssql_query ("sp_tables");
$fields = mssql_num_fields ($result);
$rows = mssql_num_rows ($result);
for ($f=0; $f<$rows; $f++) {
$CHKTYPE=GetField($result,"TABLE_TYPE",$f);
if($CHKTYPE=='TABLE'){
//$name = mssql_fetch_field($result, 2);
$field=GetField($result,"TABLE_NAME",$f)."<br>";
echo $field;
}
}
kubalaa at bigfoot dot com
05-Apr-2000 12:58
05-Apr-2000 12:58
Using this function with MSSQL 7, $returned->column_source is the column name, not the table name as it should be.