Re: [KCFusion] SQL table listing
There are a couple of cfx tags in the DevEx that you can use for this if you have access to the server to install them. CFX_TABLE http://devex.allaire.com/developer/gallery/info.cfm?ID=CA347124-2830-11D4-AA 9700508B94F380method=Full CF_TABLEFIELDS http://devex.allaire.com/developer/gallery/info.cfm?ID=CA347125-2830-11D4-AA 9700508B94F380method=Full url's will probably wrap Bryan - Original Message - From: Ryan Block [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 11:35 AM Subject: RE: [KCFusion] SQL table listing I have another question about sql tables and columns. Besides the table names, I also need to be able to get at the column names in an Access database. The folowing code works, until I get a table with no data in it, which yields a 37000 ODBC error. I've looked through Access system tables and SQL references, but can't find out how to get column names within a table. Someone please tell me what I am missing. Thanks again, Ryan Block cfif IsDefined(tablename) IS No Click the back button on your browser and select a valid table name. cfabort /cfif cfquery name=getcolumns datasource=#Client.dbname# SELECT TOP 1 * !--- Need to fix and just get one record or find other solution to get column names --- FROM #tablename# /cfquery cfoutputfont size=+1Below is a list of columns in the #tablename# table./fontbrbr Uncheck any columns that you do not want displayed on the next screen.br You may also enter a new column name that will be used when diplaying data within this application.brbr/cfoutput !--- form action=tableorfields.cfm method=post Change to this later --- form action=showdata.cfm method=post table CFLOOP INDEX=ColumnName LIST=#getcolumns.ColumnList# CFOUTPUT tr tdinput type=checkbox name=c#ColumnName# value= checked/td td#ColumnName#/td tdinput type=text name=t#ColumnName#/td /tr /CFOUTPUT /CFLOOP /table br input type=submit value=Display Data input type=Reset /form /body /html -Original Message- From: Bryan LaPlante [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 15, 2001 11:48 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] SQL table listing If you want to look at all the object in the DB here is a little dynamic table for doing that. CFQUERY NAME=QryResult DATASOURCE= MAXROWS= DBTYPE=ODBC select * from msysobjects /CFQUERY TABLE TR CFLOOP INDEX=ColumnName LIST=#QryResult.ColumnList# DELIMITERS=, TDCFOUTPUT#ColumnName#/CFOUTPUT/TD /CFLOOP CFOUTPUT QUERY=QryResult /TR TR CFLOOP INDEX=CellValue LIST=#QryResult.ColumnList# DELIMITERS=, TD#Evaluate(QryResult. #CellValue#)#/TD /CFLOOP /CFOUTPUT /TR /TABLE - Original Message - From: Ryan Block [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 15, 2001 11:01 AM Subject: [KCFusion] SQL table listing I can't remember how to query a database to just return the names of the tables in a database (just using Access for now). I know that I have seen the syntax to do this somewhere, but can't find it. Thanks in advance. Ryan Block __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] SQL table listing
Bryan Chris, You have given us great insight into finding table column names in Access. Do you have a way in SQL Server? I tried looking at some of the sysobjects type tables but they are pretty heavily populated with strange data. Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
Re: [KCFusion] SQL table listing
Look into the sp_tables and sp_columns stored procedures. - Original Message - From: Ryan Hartwich [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 12:57 PM Subject: RE: [KCFusion] SQL table listing Bryan Chris, You have given us great insight into finding table column names in Access. Do you have a way in SQL Server? I tried looking at some of the sysobjects type tables but they are pretty heavily populated with strange data. Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] SQL table listing
Ryan, This works on SQL Server 2000. You can get a specific table or set it up to look through them dynamically. select O.name, C.name from sysobjects O join syscolumns C ON C.id = O.id where O.xtype = 'U' and O.name = 'your table name here' order by C.colorder Good luck! Chris --- Ryan Hartwich [EMAIL PROTECTED] wrote: Bryan Chris, You have given us great insight into finding table column names in Access. Do you have a way in SQL Server? I tried looking at some of the sysobjects type tables but they are pretty heavily populated with strange data. Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] SQL table listing - Access
I looked at the custom tags below, but they need a fully qualified path to the database and must have DAO installed. So I'm still looking for an Access solution to get just the column names from a table through a cfquery. SELECT TOP 1 * FROM table, then getting column names using the CF ColumnList works as long as there is data in the table, but otherwise I get an error. If anyone has anymore ideas, I would appreciate it. Ryan Block -Original Message- From: Bryan LaPlante [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 17, 2001 11:48 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] SQL table listing There are a couple of cfx tags in the DevEx that you can use for this if you have access to the server to install them. CFX_TABLE http://devex.allaire.com/developer/gallery/info.cfm?ID=CA347124-2830-11D4-AA 9700508B94F380method=Full CF_TABLEFIELDS http://devex.allaire.com/developer/gallery/info.cfm?ID=CA347125-2830-11D4-AA 9700508B94F380method=Full url's will probably wrap Bryan - Original Message - From: Ryan Block [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 11:35 AM Subject: RE: [KCFusion] SQL table listing I have another question about sql tables and columns. Besides the table names, I also need to be able to get at the column names in an Access database. The folowing code works, until I get a table with no data in it, which yields a 37000 ODBC error. I've looked through Access system tables and SQL references, but can't find out how to get column names within a table. Someone please tell me what I am missing. Thanks again, Ryan Block cfif IsDefined(tablename) IS No Click the back button on your browser and select a valid table name. cfabort /cfif cfquery name=getcolumns datasource=#Client.dbname# SELECT TOP 1 * !--- Need to fix and just get one record or find other solution to get column names --- FROM #tablename# /cfquery cfoutputfont size=+1Below is a list of columns in the #tablename# table./fontbrbr Uncheck any columns that you do not want displayed on the next screen.br You may also enter a new column name that will be used when diplaying data within this application.brbr/cfoutput !--- form action=tableorfields.cfm method=post Change to this later --- form action=showdata.cfm method=post table CFLOOP INDEX=ColumnName LIST=#getcolumns.ColumnList# CFOUTPUT tr tdinput type=checkbox name=c#ColumnName# value= checked/td td#ColumnName#/td tdinput type=text name=t#ColumnName#/td /tr /CFOUTPUT /CFLOOP /table br input type=submit value=Display Data input type=Reset /form /body /html -Original Message- From: Bryan LaPlante [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 15, 2001 11:48 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] SQL table listing If you want to look at all the object in the DB here is a little dynamic table for doing that. CFQUERY NAME=QryResult DATASOURCE= MAXROWS= DBTYPE=ODBC select * from msysobjects /CFQUERY TABLE TR CFLOOP INDEX=ColumnName LIST=#QryResult.ColumnList# DELIMITERS=, TDCFOUTPUT#ColumnName#/CFOUTPUT/TD /CFLOOP CFOUTPUT QUERY=QryResult /TR TR CFLOOP INDEX=CellValue LIST=#QryResult.ColumnList# DELIMITERS=, TD#Evaluate(QryResult. #CellValue#)#/TD /CFLOOP /CFOUTPUT /TR /TABLE - Original Message - From: Ryan Block [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 15, 2001 11:01 AM Subject: [KCFusion] SQL table listing I can't remember how to query a database to just return the names of the tables in a database (just using Access for now). I know that I have seen the syntax to do this somewhere, but can't find it. Thanks in advance. Ryan Block __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED
RE: [KCFusion] SQL table listing - Access
Hey, Does anyone know about a virus having to do with Sri Lanka? -Original Message- From: Ryan Block [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 17, 2001 2:13 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] SQL table listing - Access But I still need to be able to access those columns to input data, even though they are empty. Why don't you do a count on the number of records in the table first. If the recordcount = 0 don't run the CF ColumnList program. Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] SQL table listing
Ryan, Depending on how often you run update stats on the SQL database you might be able to use this. This will loop through all databases on sysdatabases and then get all the tables in the database and finally list all of the columns in the order they should be. DECLARE @ls_db varchar(255), @ls_sql varchar(255) SELECT @ls_db = null SELECT @ls_db = Min(name) FROM master.dbo.sysdatabases WHILE @ls_db is not null BEGIN SELECT @ls_db SELECT @ls_sql = 'select O.name, C.name from ' + @ls_db + '.dbo.sysobjects O join syscolumns C ON C.id = O.id where O.xtype = U order by O.name, C.colorder' EXEC ( @ls_sql) SELECT @ls_db = Min(name) FROM master.dbo.sysdatabases WHERE name @ls_db END Again, good luck!!! Chris --- Ryan Hartwich [EMAIL PROTECTED] wrote: Chris, Your SQL code to do a table lookup and extract the column names works on SQL 7 also. Thanks a lot! Any permutations to do output all the table names in a database/datasource? Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
Re: [KCFusion] SQL table listing - Access
http://www.google.com/search?q=sri+lanka+virus - Original Message - From: Nathan T Haley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 2:13 PM Subject: RE: [KCFusion] SQL table listing - Access Hey, Does anyone know about a virus having to do with Sri Lanka? -Original Message- From: Ryan Block [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 17, 2001 2:13 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] SQL table listing - Access But I still need to be able to access those columns to input data, even though they are empty. Why don't you do a count on the number of records in the table first. If the recordcount = 0 don't run the CF ColumnList program. Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] SQL table listing - Access
Cute, but not the right kind of virus. Got it sorted, thanks. -Original Message- From: Daryl Banttari [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 17, 2001 2:37 PM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] SQL table listing - Access http://www.google.com/search?q=sri+lanka+virus - Original Message - From: Nathan T Haley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 2:13 PM Subject: RE: [KCFusion] SQL table listing - Access Hey, Does anyone know about a virus having to do with Sri Lanka? -Original Message- From: Ryan Block [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 17, 2001 2:13 PM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] SQL table listing - Access But I still need to be able to access those columns to input data, even though they are empty. Why don't you do a count on the number of records in the table first. If the recordcount = 0 don't run the CF ColumnList program. Ryan __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
[KCFusion] SQL table listing
I can't remember how to query a database to just return the names of the tables in a database (just using Access for now). I know that I have seen the syntax to do this somewhere, but can't find it. Thanks in advance. Ryan Block __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
Re: [KCFusion] SQL table listing
Do this before you run your query. Go to Access and open your DB, go to the tools menu and choose security groups and permissions, Click the checkbox for administer on the msysobjects table. cfquery select name from msysobjects where type = 1 /cfquery type = 1 are tables and type = 5 are views Bryan - Original Message - From: Ryan Block [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 15, 2001 11:01 AM Subject: [KCFusion] SQL table listing I can't remember how to query a database to just return the names of the tables in a database (just using Access for now). I know that I have seen the syntax to do this somewhere, but can't find it. Thanks in advance. Ryan Block __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
RE: [KCFusion] SQL table listing
Thanks. That works great. -Original Message- From: Bryan LaPlante [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 15, 2001 11:48 AM To: [EMAIL PROTECTED] Subject: Re: [KCFusion] SQL table listing If you want to look at all the object in the DB here is a little dynamic table for doing that. CFQUERY NAME=QryResult DATASOURCE= MAXROWS= DBTYPE=ODBC select * from msysobjects /CFQUERY TABLE TR CFLOOP INDEX=ColumnName LIST=#QryResult.ColumnList# DELIMITERS=, TDCFOUTPUT#ColumnName#/CFOUTPUT/TD /CFLOOP CFOUTPUT QUERY=QryResult /TR TR CFLOOP INDEX=CellValue LIST=#QryResult.ColumnList# DELIMITERS=, TD#Evaluate(QryResult. #CellValue#)#/TD /CFLOOP /CFOUTPUT /TR /TABLE - Original Message - From: Ryan Block [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 15, 2001 11:01 AM Subject: [KCFusion] SQL table listing I can't remember how to query a database to just return the names of the tables in a database (just using Access for now). I know that I have seen the syntax to do this somewhere, but can't find it. Thanks in advance. Ryan Block __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED] __ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe mailto:[EMAIL PROTECTED] To Unsubscribe mailto:[EMAIL PROTECTED]
