Solved

CFQuery on a whole Database at once??

Posted on 2003-12-07
12
512 Views
Last Modified: 2013-12-24
Is there a way with cold fusion and SQL to do a CFQuery of a whole database only knowing the DSN and name of database.  the
tables in this database would be dynamically created which is why the name of amount of tables would be unknown.  Thanks Joe.
0
Comment
Question by:joebox
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 12

Expert Comment

by:jyokum
Comment Utility
what kind of database?
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
Yes u can !

u can get the list of all the tables & u cld query each of them individually to get there columnnames as well !

if u can let us know ur DB - we cld help u out !

K'Rgds
Anand
0
 

Author Comment

by:joebox
Comment Utility
SQL 2000
0
 
LVL 11

Expert Comment

by:hart
Comment Utility
u can query the sysobjects in sql 2000 to get the user defined tables..

that is

select name from sysobjects where xtype = 'U'
will give all the user defined tables...

run this query in the query analyser and see..

Regards
Hart


0
 
LVL 11

Expert Comment

by:hart
Comment Utility
sorry a small change use this query

select * from sysobjects where xtype = 'U' and status > 1

because there are other system tables with xtype as U but their status is negative

so all u have to do is this
create a dsn with username and password that has access to system tables


and use the above query in the cf query.

Regards
Hart
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
there is a tag for this created by JIMMY [a member of EE] called CF_DBINFO

it has 2 files Dbinfo.cfm :
<!---
  ===================================================================
  utility:          cf_dbinfo
  author:           anuj gakhar
  email:            jimmy_282@yahoo.com
  ===================================================================
  description:

  this tag will give you all the information about your MSACCESS,MSSQL and MYsql databases.
  For MSAccess:
          tables,queries,reports,forms,modules and macros
For MSSQL:
            tables, triggers, stored procedures.                  
For Mysql:
            tables      
 
  For MSSQL and Mysql:
        this tag can return column names and types of a particular table or yield info abt a sp or trigger.

For MSACESS:
  this tag makes use of the msysobjects system object lying inside
  your databse. if you get an error like
  "there is no read permission on msysobjects"
  while executing this tag, then follow these steps.
  1) open your .mdb file in microsoft access.
  2) go to tools->security->user and group permissions.
  3) click on msysobjects and assign it the "read data" permission.
 
  now, if you don't see msysobjects in "user group and permissions"
  follow these steps.
  1)go to tools->options->view.
  2)check the "system objects" checkbox.
  3)click ok.
 
  _______________________________________________
  Keep this header IN here if you are using this tag
  Written By : Anuj Gakhar
  E-mail : anuj@the-whizkids.com
  ________________________________________________  
--->
      <CFPARAM name="proceed" default="yes">
      <CFPARAM name="error_message" default="">
      <CFPARAM name="attributes.dsn" default="">
      <CFPARAM name="attributes.dbusername" default="">
      <CFPARAM name="attributes.dbpassword" default="">
      <CFPARAM name="attributes.dbtype" default="MSACCESS">
      <CFPARAM name="attributes.object" default=""><!--- check for required attribute --->            
      <CFIF (isdefined("attributes.dsn") is "no")>            
            <CFSET proceed = "no">            
            <CFSET error_message = "dsn attribute is required!">
      </CFIF>
      <!--- check that required attribute is not empty --->            
      <CFIF trim("#attributes.dsn#") eq "">            
            <CFSET proceed = "no">            
            <CFSET error_message = "dsn attribute should not be left blank!">
      </CFIF>
                  <CFIF not Listfind("MSACCESS,MSSQL,MYSQL","#attributes.dbtype#")>            
            <CFSET proceed = "no">            
            <CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported">
      </CFIF>
                  <CFIF proceed>            <!--- send a dummy query to the db to test the connectivity --->
            <CFTRY>
            <CFQUERY datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select 1;
            </CFQUERY>
            <CFCATCH type="database">            
            <CFSET proceed = "no">            
            <CFSET error_message = "#cfcatch.message#"></CFCATCH></CFTRY>
      </CFIF>
      <!--- everything seems to be fine, so let them in --->            
      <CFIF proceed>
            <CFSWITCH expression="#attributes.dbtype#">
            <CFCASE value="MSACCESS"><!--- tables --->
            <CFQUERY name="gettables" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (left$([name],4) <> 'msys')
                              and (msysobjects.type)=1
                              order by msysobjects.name;
            </CFQUERY>
            <!--- add the tables to a list --->            
            <CFSET tables = "">
            <CFLOOP query="gettables">
                                    <CFSET tables= #listappend(tables,#name#)#>
            </CFLOOP>
            <!--- send this list to the caller template --->            
            <CFSET caller.tables = tables><!--- queries --->
            <CFQUERY name="getqueries" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)=5
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET queries = "">
            <CFLOOP query="getqueries">
                                    <CFSET tables= #listappend(queries,#name#)#>
            </CFLOOP>
                        <CFSET caller.queries = queries><!--- forms --->
            <CFQUERY name="getforms" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)=-32768
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET forms="">
            <CFLOOP query="getforms">
                                    <CFSET forms = #listappend(forms,#name#)#>
            </CFLOOP>
                        <CFSET caller.forms = forms><!--- reports --->
            <CFQUERY name="getreports" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)= -32764
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET reports="">
            <CFLOOP query="getreports">
                                    <CFSET reports = #listappend(reports,#name#)#>
            </CFLOOP>
                        <CFSET caller.reports=reports><!--- modules --->
            <CFQUERY name="getmodules" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)= -32761
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET modules="">
            <CFLOOP query="getmodules">
                                    <CFSET modules = #listappend(modules,#name#)#>
            </CFLOOP>
                        <CFSET caller.modules=modules><!--- macros --->
            <CFQUERY name="getmacros" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select msysobjects.name from msysobjects where
                              (left$([name],1)<>'~')
                              and (msysobjects.type)= -32766
                              order by msysobjects.name;
            </CFQUERY>
                        <CFSET macros="">
            <CFLOOP query="getmacros">
                                    <CFSET macros = #listappend(macros,#name#)#>
            </CFLOOP>
                        <CFSET caller.macros=macros></CFCASE>
            <CFCASE value="MSSQL">                  
            <CFIF not len("#attributes.object#")>                  <!--- tables --->
                  <CFQUERY name="gettables" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name ;
                  </CFQUERY>
                  <!--- add the tables to a list --->                  
                  <CFSET tables = "">
                  <CFLOOP query="gettables">
                                                <CFSET tables= #listappend(tables,#name#)#>
                  </CFLOOP>
                  <!--- send this list to the caller template --->                  
                  <CFSET caller.tables = tables><!--- stored Procedures --->
                  <CFQUERY name="getsps" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects
                                WHERE type='P' AND
                                ObjectProperty(ID,'IsMSShipped')=0 ;
                  </CFQUERY>
                                    <CFSET sps = "">
                  <CFLOOP query="getsps">
                                                <CFSET #listappend(sps,#name#)#>
                  </CFLOOP>
                                    <CFSET caller.sps = sps><!--- Triggers --->
                  <CFQUERY name="gettriggers" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects WHERE xtype='TR';
                  </CFQUERY>
                                    <CFSET triggers = "">
                  <CFLOOP query="gettriggers">
                                                <CFSET #listappend(triggers,#name#)#>
                  </CFLOOP>
                                    <CFSET caller.triggers = triggers>
            <CFELSE>
<!--- see the object passes is a table or sp or trigger--->
                  <CFQUERY name="ch" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        SELECT name FROM sysobjects WHERE xtype='u' AND name='#trim(attributes.object)#' ORDER BY name ;
                  </CFQUERY>
                                          <CFIF ch.recordcount>                        <!--- Its a table --->
                        <CFTRY>
                        <CFQUERY name="ch1" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                              exec sp_columns @table_name='#trim(attributes.object)#';
                        </CFQUERY>
                        <CFCATCH type="database">                        
                        <CFSET proceed="no">
                        <CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
                        </CFOUTPUT>
                        <CFABORT></CFCATCH></CFTRY>
                        <CFLOOP query="ch1">
                              <CFOUTPUT>
                                    #ch1.column_name# --- #ch1.type_name#                                          
                                    <CFIF #ch1.type_name# EQ "varchar" OR #ch1.type_name# EQ "char">                                          (#ch1.length#)
                                    </CFIF>
<BR>
                              </CFOUTPUT>
                        </CFLOOP>
                  <CFELSE>
<!--- Its a SP or Trigger --->
                        <CFTRY>
                        <CFQUERY name="ch1" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                              exec sp_helptext '#trim(attributes.object)#';
                        </CFQUERY>
                        <CFCATCH type="database">                        
                        <CFSET proceed="no">
                        <CFOUTPUT>
<STRONG>DBINFO Error</STRONG>: #cfcatch.message#
                        </CFOUTPUT>
                        <CFABORT></CFCATCH></CFTRY>
                        <CFLOOP query="ch1">
                              <CFOUTPUT>
                                    #ch1.text#<BR>
                              </CFOUTPUT>
                        </CFLOOP>
                  </CFIF>
            </CFIF>
</CFCASE>
            <CFCASE value="MYSQL">
            <CFQUERY name="getdb" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                  select database() as d;
            </CFQUERY>
                              <CFIF not len("#attributes.object#")>                  <!--- tables --->
                  <CFQUERY name="gettables" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        show tables ;
                  </CFQUERY>
                  <!--- add the tables to a list --->                  
                  <CFSET tables = "">
                  <CFLOOP query="gettables">
                                                <CFSET tables= #listappend(tables,#Evaluate("tables_in_" & #getdb.d#)#)#>
                  </CFLOOP>
                  <!--- send this list to the caller template --->                  
                  <CFSET caller.tables = tables>
            <CFELSE>
                  <CFQUERY name="ch1" datasource="#attributes.dsn#" username="#attributes.dbusername#" password="#attributes.dbpassword#">
                        describe #trim(attributes.object)#;
                  </CFQUERY>
<STRONG>Field - Type - Null - Key - Default - Extra</STRONG><BR>
                  <CFLOOP query="ch1">
                        <CFOUTPUT>
                              #field# - #type# - #null# - #key# - #default# - #extra#<BR>
                        </CFOUTPUT>
                  </CFLOOP>
            </CFIF>
</CFCASE>
            <CFDEFAULTCASE>            
            <CFSET proceed = "no">            
            <CFSET error_message = "the parameter DBTYPE is Invalid. Only MSACCESS,MSSQL and MYSQL are supported"></CFDEFAULTCASE></CFSWITCH>
      <CFELSE>
            <CFOUTPUT>
<H4>dbinfo error!<BR>#error_message#</H4>
            </CFOUTPUT>
            <CFABORT>
      </CFIF>

and dbinfotest.cfm :
<!--- Some Example Usage
FOR MSACCESS
<cf_dbinfo dsn="test" dbusername="sa" dbpassword="123">
no need of dbtype here - default is MSACCESS
----------------------------------------
FOR MSSQL
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="mytable">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyStoredProc">
OR
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123" object="MyTrigger">
-------------------------------------
FOR MYSQL
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="root" dbpassword="">
OR
<cf_dbinfo dsn="test" dbtype="MYSQL" dbusername="sa" dbpassword="123" object = "MyTable">
-------------------------------------
__________________________________________________
All Errors, Bugs, Feature Requests, Comments to anuj@the-whizkids.com
___________________________________________________
ENJOY!
--->
<cf_dbinfo dsn="test" dbtype="MSSQL" dbusername="sa" dbpassword="123">

<cfoutput>
<!--- Display Tables --->
<cfif isdefined("tables")>
<cfif listlen(tables)>
<strong><u>Tables</u></strong><br>
<cfloop from="1" to="#listlen(tables)#" index="i">
#ListGetAt(tables,"#i#")# <br>
</cfloop>
<cfelse>
No Tables!
</cfif>
</cfif>
<br>
<!--- Display Queries --->
<cfif isdefined("queries")>
<cfif listlen(queries)>
<strong><u>Queries</u></strong><br>
<cfloop from="1" to="#listlen(queries)#" index="i">
#ListGetAt(queries,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Queries!</strong>
</cfif>
</cfif>
<br>
<!--- Display Reports --->
<cfif isdefined("reports")>
<cfif listlen(Reports)>
<strong><u>Reports</u></strong><br>
<cfloop from="1" to="#listlen(reports)#" index="i">
#ListGetAt(reports,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Reports!</strong>
</cfif>
</cfif>
<br>
<!--- Display Forms --->
<cfif isdefined("forms")>
<cfif listlen(Forms)>
<strong><u>Forms</u></strong><br>
<cfloop from="1" to="#listlen(forms)#" index="i">
#ListGetAt(forms,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Forms!</strong>
</cfif>
</cfif>
<br>
<!--- Display Macros --->
<cfif isdefined("macros")>
<cfif listlen(Macros)>
<strong><u>Macros</u></strong><br>
<cfloop from="1" to="#listlen(macros)#" index="i">
#ListGetAt(macros,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Macros!</strong>
</cfif>
</cfif>
<br>
<!--- Display Modules --->
<cfif isdefined("modules")>
<cfif listlen(Modules)>
<strong><u>Modules</u></strong><br>
<cfloop from="1" to="#listlen(modules)#" index="i">
#ListGetAt(modules,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Modules!</strong>
</cfif>
</cfif>

<!--- Display Stored Procedures --->
<cfif isdefined("sps")>
<cfif listlen(sps)>
<strong><u>Stored Procedures</u></strong><br>
<cfloop from="1" to="#listlen(sps)#" index="i">
#ListGetAt(sps,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Stored procedures!</strong>
</cfif>
</cfif>

<!--- Display Stored Procedures --->
<cfif isdefined("triggers")>
<cfif listlen(triggers)>
<strong><u>Triggers</u></strong><br>
<cfloop from="1" to="#listlen(triggers)#" index="i">
#ListGetAt(triggers,"#i#")# <br>
</cfloop>
<cfelse>
<strong>No Triggers!</strong>
</cfif>
</cfif>
</cfoutput>

save these files on ur pc & have a look at it !

hth

K'Rgds
Anand
0
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
LVL 17

Accepted Solution

by:
Tacobell777 earned 500 total points
Comment Utility
Wow wow whohoaaaa, can I just suggest the system stored procedures that were made to do this kind of thing...

have a look at sp_tables in the documentation, and sp_columns they are fancier and better to work with..
0
 

Author Comment

by:joebox
Comment Utility
Hey Tacobell777

I see the stored procedure under the Master DAtabase.  it is sp_tables like you said.  Im am very good at Cold Fusion but a newbie to MS SQL 2000, How would I implement the sp_tables??  Exactly what i want to do is create a CFQuery that will search all tables inn the Datasource given.  One factor is that I would not know how many or the names of the tables and secondly not all tables would have the column needed to search.  I know this is a tall order but I will be willing to give more points to this if there is a way to add them on.    Thanks for all the help... Joe
0
 
LVL 11

Expert Comment

by:hart
Comment Utility
did u check other posts ????

Regards
Hart
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
JoeBox - did u even look at the TAG ???
0
 
LVL 4

Expert Comment

by:mrmcfeely
Comment Utility
I agree with Tacobell on this one... the system stored procedures are so far the best option.

The tag is cool, but like some of the other solutions presented, it hits the sysobjects table directly... this is somewhat of a SQL Server no-no, since the system tables may change from release to release.  This is straight out of SQL Server Books Online (under "Information Schema Views"):

"Note  To obtain meta data, use system stored procedures, system functions, or these system-supplied views only. Querying the system tables directly may not provide accurate information if system tables are changed in future releases."

To mitigate this risk of the data model being pulled out from under your CF code, SQL Server provides a set of system stored procedures, as Tacobell mentioned.  There are also some functions, and a set of views (look up "Information Schema Views" in books online) that conform to the SQL-92 standard and should always work, no matter what changes happen to the underlying system tables.

Anand... I think it would be pretty cool if the custom tag were changed to use the INFORMATION_SCHEMA views!  If you get bored...
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
Yeah ...

Its upto JoeBox - if he wants to write a fresh code or
Use the given solution & optimise it as per "books online".

The day i get time & I am in desperate need of it ... i'll do it myself :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now