[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

CFQuery on a whole Database at once??

Posted on 2003-12-07
12
Medium Priority
?
557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9892086
what kind of database?
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9892555
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
ID: 9892571
SQL 2000
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 11

Expert Comment

by:hart
ID: 9894603
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
ID: 9894640
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
ID: 9894679
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
 
LVL 17

Accepted Solution

by:
Tacobell777 earned 2000 total points
ID: 9895354
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
ID: 9902827
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
ID: 9902859
did u check other posts ????

Regards
Hart
0
 
LVL 17

Expert Comment

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

Expert Comment

by:mrmcfeely
ID: 9904863
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
ID: 9918230
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

649 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