RE: Find out table names and fields?

Eric Creese Wed, 07 Jul 2004 14:12:15 -0700

for MS SQL I use the following and dump to a table caled Dictionary

SELECT o.name AS TableName,c.name AS FieldName,t.name AS FieldDataType,t.length AS FieldLength,NULLS = CASE c.isnullable
      WHEN 1 THEN 'NULL'
      ELSE 'NOT NULL'
      END AS Nullable
INTO Dictionary
FROM sysobjects o, syscolumns c,systypes t
WHERE o.id = c.id
AND c.xtype = t.xtype
AND o.type = 'U'
AND o.name not like 'sys%'
AND o.name not like 'ms%'
AND o.name not like 'dt%'
AND t.name <> 'sysname'
ORDER BY o.name

Then in a CF page I call it and dispaly it
<HTML>
<HEAD>

<cfquery name="gettabs" datasource="dictionary">
SELECT DISTINCT tablename FROM dictionary
ORDER BY tableName
</cfquery>

<cfquery name="getdic" datasource="dictionary">
SELECT * FROM dictionary
ORDER BY tableName,fieldname
</cfquery>

<SCRIPT LANGUAGE="_javascript_">

function goTo() {
    var selIdx = document.SearchForm.cbSearchSelect.selectedIndex;
    location.hash =
document.SearchForm.cbSearchSelect.options[selIdx].value;
}

</script>

<SCRIPT language="_javascript_">

</SCRIPT>
</HEAD>

<body>

<FORM NAME="SearchForm" >
<INPUT type="text" name="edSeachInput" searchSelectBox('SearchForm', 'edSeachInput', 'cbSearchSelect')"><BR>
<select NAME="cbSearchSelect" > <cfoutput query="gettabs">
<option value="###tablename#">#UCase(tablename)#</option>
</cfoutput>
</select> <input type="button" value="go" > </FORM>

<p>

<cfoutput query="getdic" group="tablename">
<table width="760" valign="top">
  <tr valign="top">
   <td valign="top" colspan=4><b><a name="#tablename#">#UCase(tablename)#</a></b></td>
  </tr>
  <tr bgcolor="cccccc">
   <td valign="top" width="30%"><font size=2>Column</font></td>
   <td valign="top" width="15%"><font size=2>Data Type</font></td>
   <td valign="top" width="5%"><font size=2>Size</font></td>
   <td valign="top" width="50%"><font size=2>Nullable</font></td>
  </tr>
<cfoutput>
  <tr>
   <td valign="top"><font size=2>#fieldname#</font></td>
   <td valign="top"><font size=2>#fielddatatype#</font></td>
   <td valign="top"><font size=2>#fieldlength#</font></td>
   <td valign="top"><font size=2>#nullable#</font></td>
  </tr>
</cfoutput>
<tr><td colspan=4><a href="" ><font color="red" size="1">back to top</font></a><br> </td></tr>
</table>
</cfoutput>

</body>
</html>

-----Original Message-----
From: brobborb [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 07, 2004 3:49 PM
To: CF-Talk
Subject: Re: Find out table names and fields?

how do i dump the results to display on the page?  Will this only work for mySQL, or for MS SQL as well?

thanks!
  ----- Original Message -----
  From: Barney Boisvert
  To: CF-Talk
  Sent: Wednesday, July 07, 2004 3:42 PM
  Subject: RE: Find out table names and fields?

  Sure, run these queries, and dump the result.

  `show databases` will list all databases you have access to via the DSN.
  `show tables` will list all tables in the database the DSN is connected to.
  `show tables in <dbname>` will list all tables in the named database.
  `explain <tablename>` will list the columns of the named table.  You can use
  cross-database tables by saying <dbname>.<tablename>

  Cheers,
  barneyb

  > -----Original Message-----
  > From: brobborb [mailto:[EMAIL PROTECTED]
  > Sent: Wednesday, July 07, 2004 1:26 PM
  > To: CF-Talk
  > Subject: Find out table names and fields?
  >
  > Hey guys, I have a host that has no web admin support for
  > mySQL.  I mean, they have MySQL running, but no admin.  At
  > least thats what I'm told by my client. I was wondering if
  > there was a way to extract the table names and fields using
  > coldfusion instead.  Would that be possible?
  >
  >
  _____

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

RE: Find out table names and fields?

Reply via email to