If you want to describe table like DESCRIBE in MySQL you can use this:
$sql = <<<SQL
SELECT column_name,data_type,column_default,is_nullable
FROM
information_schema.tables AS t
JOIN
information_schema.columns AS c ON
t.table_catalog=c.table_catalog AND
t.table_schema=c.table_schema AND
t.table_name=c.table_name
WHERE
t.table_name='TABLE-NAME'
SQL;
mssql_fetch_field
(PHP 4, PHP 5, PECL odbtp >= 1.1.1)
mssql_fetch_field — Obtiene información del campo
Advertencia
Esta función está OBSOLETA en PHP 7.0.0.
Las alternativas a esta función son:
- PDOStatement::getColumnMeta()
- sqlsrv_field_metadata()
- Las funciones odbc_field_* del controlador de ODBC unificado.
Descripción
mssql_fetch_field
( resource
$result
[, int $field_offset = -1
] ) : objectmssql_fetch_field() puede utilizarse para obtener información sobre los campos de una determinada consulta realizada.
Parámetros
-
result -
El resultado que está siendo evaluado. Este resultado proviene de una llamada a la función mssql_query().
-
field_offset -
El número de posición del campo. Si no se especifica, se mostrará el próximo campo que todavía no se haya mostrado. El parámetro
field_offsetempieza en 0.
Valores devueltos
Devuelve un objeto que contiene la información de los campos.
Las propiedades del objeto son las siguientes:
- name - nombre de columna. Si la columna es el resultado de una función, esta propiedad toma el valor computed#N, donde #N es un número de serie.
- column_source - la tabla de la que proviene la columna.
- max_length - longitud máxima de la columna.
- numeric - 1 si la columna es numérica.
- type - el tipo de la columna.
Ejemplos
Ejemplo #1 mssql_fetch_field() example
<?php
// Conectar a MSSQL y seleccionar la base de datos
mssql_connect('MANGO\SQLEXPRESS', 'sa', 'phpfi');
mssql_select_db('php');
// Enviar una selección a MSSQL
$query = mssql_query('SELECT * FROM [php].[dbo].[persons]');
// Construir la tabla
echo '<h3>Estructura de tabla para \'persons\'</h3>';
echo '<table border="1">';
// Cabecera de la tabla
echo '<thead>';
echo '<tr>';
echo '<td>Field name</td>';
echo '<td>Data type</td>';
echo '<td>Max length</td>';
echo '</tr>';
echo '</thead>';
// Descargar los campos
echo '<tbody>';
for ($i = 0; $i < mssql_num_fields($query); ++$i) {
// Leer la información del campo
$field = mssql_fetch_field($query, $i);
// Mostrar la fila
echo '<tr>';
echo '<td>' . $field->name . '</td>';
echo '<td>' . strtoupper($field->type) . '</td>';
echo '<td>' . $field->max_length . '</td>';
echo '</tr>';
}
echo '</tbody>';
echo '</table>';
// Liberar el recurso
mssql_free_result($query);
?>
add a note
User Contributed Notes 10 notes
schattenfeld at gmail dot com ¶
16 years ago
huszti_dot_roland_at_freemail_dot_com ¶
17 years ago
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.
php_rindern_de ¶
19 years ago
commenting Reynard Hilman:
for me it looks like the colstat field value of 1 in syscolumns table indicates an Identity Column.
Reynard Hilman ¶
20 years ago
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 ¶
21 years ago
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 ¶
21 years ago
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 ¶
22 years ago
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.
kubalaa at bigfoot dot com ¶
24 years ago
Using this function with MSSQL 7, $returned->column_source is the column name, not the table name as it should be.
not a mail address ¶
15 years ago
kubalaa at bigfoot dot com first reported the problem that column_source contains the FIELD name instead of the source TABLE name 04-Apr-2000 03:58. The bug has been reported as well.
For those who wonder if it was fixed over the past 8 years: No, the bug still is the same, no workaround available.
skipsey at hotmail dot com ¶
23 years ago
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.
<?php
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;
}
}
?>
