SV: [PHP-WIN] Retrieving Database Table Names
| From: | Henrik Hornemann | Date: | Thu, 18 Sep 2003 13:49:03 +0000 |
| Subject: | SV: [PHP-WIN] Retrieving Database Table Names | ||
| Groups: | php.windows | ||
| Request: | Send a blank email to php-windows+get-21425@lists.php.net to get a copy of this message | ||
Hi,
Here is a script that I use to pull this and some other information
mssql_select_db("$db");
$res=mssql_query("select name,id from sysobjects where type='u' order by
name");
while (list($table,$id) = mssql_fetch_row($res)) {
$res2 = mssql_query("select count(*) from $table");
list($rows)=mssql_fetch_row($res2);
$res2=mssql_query("select syscolumns.name, systypes.name as type, syscolumns.length
from syscolumns join systypes on syscolumns.xtype=systypes.xusertype
where syscolumns.id =$id order by colid");
echo "<table border=1><tr><td
width=200><b>$table</b><br> - ialt $rows poster</td><td
width=300>";
echo "<table>";
while (list($name,$type,$size) = mssql_fetch_row($res2)) {
$res3=@mssql_query("select count(*) from $table where $name > '
'") or
$res3=mssql_query("select count(*) from $table where $name is not null");
list($comp) = mssql_fetch_row($res3);
$comp = ($rows > 0)?round($comp/$rows*100,1):100;
echo "<tr ><td height=8>$name</td><td>$type [$size]
$comp % </td></tr>";
}
echo "</table></td></tr></table></body>";
}
hth Henrik Hornemann
> -----Oprindelig meddelelse-----
> Fra: Herhuth, Ron [mailto:Ron.Herhuth@tatumpartners.com]
> Sendt: 18. september 2003 15:07
> Til: php-windows@lists.php.net
> Emne: [PHP-WIN] Retrieving Database Table Names
>
>
>
> I am trying to build a dynamic database map that will return
> a page that
> shows each Table in the database along with the Column names and the
> datatype of each column in a rather large Microsoft SQL
> Server database.
> I know how to extract the column names for the tables, as well as the
> datatypes of the column...but for the life of me I can't
> figure out the
> SQL query to use to retrieve a list of the tables a database
> contains. It
> is important that the script be able to account for changes to the
> database.
>
> Does anyone know the SQL to extract the user created tables in a MSSQL
> database?
>
> Thanks,
> Ron
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>