SQL Selecting tables from DB

[DJMK4]

Can i use an SQL query to select all tables from the database?

SELECT * Tables from DB

 

[Haircut]

Not like that but there will probably be a way to do it.
It is different on different databases though, what are you using.

e.g. in Oracle it would be SELECT * FROM all_tables

 

[DJMK4]

How do you mean?

Im using SQLYog (A GUI programme that lets you manage the database) the SQL Database itself is located on the SQL server of my web-hosting company.

 

[Augmented]

The syntax can vary between database systems - Oracle being one type of database system, MySQL being another and MSSQL being a third. You haven't specified what database system you are using.

Are you trying to extract data from multiple tables, or just get a list of all the tables in a database? For the latter, you can run the query

SHOW TABLES;

in MySQL to return that information.

 

[DJMK4]

How do i find out what type of database im running on? as i said its running on the web-host i am using and im using a 3rd party package (GUI) that lets me manage the database, then i use PHP as a web-front end to run the SQL query's

<?
$username="";
$password="";
$database="";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SHOW TABLES";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo  "<b><center>Database Output</center></b><br><br>";




$i=0;
while ($i < $num) {

$catagory=mysql_result($result,$i,"catagory");
$title=mysql_result($result,$i,"title");
$price=mysql_result($result,$i,"price");











$i++;
}

?>

how would i change

$catagory=mysql_result($result,$i,"catagory");
$title=mysql_result($result,$i,"title");
$price=mysql_result($result,$i,"price");

to hold the output for the table information? because it dont contain any fields? its just the table names?

 

[Clarkey]

read the php manual, im fairly sure there is a function to list the tables. Thats a mysql database anyway, the references to 'mysql' in the php code give it away

 

[Beansprout]

You use MySQL

Just incase you forgot from last night, SHOW TABLES outputs a result set with one column, and each row contains a table name.

 

[Borris]

select name from sysobjects where name like '%'

Wouldn't that work too?

 

[happytechie]

I think sysobjects is an SQL Server system view and shows a lot more than the tables in the current database?

SELECT name
FROM sysobjects
WHERE type = 'U';

will give you the user tables on SQL Server.

HT

 

[DJMK4]

Ok, iv managed to pull the table names from the database and iv worked out how to change them to links with abit of help from beansprout.


www.caldicot-pc-helpdesk.co.uk/fcv/database/index3.php

by using the following code

while($row = mysql_fetch_row($result))
{
echo '<a href="index8.htm">'.$row[0].'</a>';

echo "<br />";
}

I want to be able to click this link which is located in 1 div, and the table contents (when clicked) will appear in another div.

What will i have to do to get this? i cant remember the code for targeting a link to another part of the page.