I have a database in sql server 2008 express. Some tables have columns with default value. I am writing php code to pull those table names, columns and their default values. Here is the part of code that deals with it:
<?php
$query = ‘select t.name as table_name,
c.name as column_name,
cast(dc.definition as text) as def_value
from sys.default_constraints dc, sys.columns c, sys.tables t
where c.column_id = dc.parent_column_id
and c.object_id = dc.parent_object_id
and c.object_id = t.object_id
order by 1, 2’;
$oResultSet = mssql_query($query, $dbconnection);
$rowCount=mssql_num_rows($oResultSet);
for($i=0; $i < $rowCount; $i++) {
$row=mssql_fetch_assoc($oResultSet);
foreach ($row as $key => $value)
echo “$key = $value <br>”;
}
?>
This script returns table_name and column_name correctly, but it always returns null for def_value even when there are values in that column.
When I run the same ‘$query’ in SQL Server Management Studio,
it shows values in def_value column.
I had to use cast in the select statement because it appears
that dc.definition column of sys.default_constraints is defined as ntext, and
I was getting an error "Unicode data in a Unicode-only collation or ntext data
cannot be sent to clients using DB-Library (such as ISQL) or ODBC
version 3.7 or earlier. "
I tried casting dc.definition with varchar(50), char(100) but the results
were the same.
Any help would be greatly appreciated.
Thanks,
Alex.