Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
Need help trying to do with SQL2000 what this code does using Mysql, I don't have a clue on how to do it to access all the table names in SQL2000 and print all the names to the browser screen, any help please.....

$db = Mysql->connect($host, $database, $user, $password);

$db->selectdb($database);

@tables = $db->listtables;

foreach $table (@tables) {
print $table."\n<BR>";
}

[download]

Thanks very much!!!!!

Replies are listed 'Best First'.
Re: Database Table Names
by Zaxo (Archbishop) on Nov 07, 2003 at 21:04 UTC

    Your db code will become more portable if you rewrite it for DBI. In DBI the corresponding call is to table_info() or tables(). See the DBI docs for details.

    After Compline,
    Zaxo

[reply]
Re: Database Table Names
by jZed (Prior) on Nov 07, 2003 at 20:58 UTC
    Look up the table_info() method in the DBI docs.
[reply]
Re: Database Table Names
by bilfurd (Hermit) on Nov 07, 2003 at 22:10 UTC
    DBI & ODBC are very handy when dealing with SQL Server (I use it whenever possible), but in the spirit of TMTOWTDI --

    In the database, there is a table called sysobjects that stores information about all the objects in that database. Each object type (key, table, index, etc.) has a specific code. These codes are stored in the xtype field. So... 

    SELECT name
FROM sysobjects
WHERE xtype='U'
ORDER BY name

    [download]

    ... should return a list of all table names in the database.

    Cheers

[reply]
[d/l]
      I know how to access any table in a given database but what I am looking for is to get all the names of the tables inside of the data base and then from my selection my program will read the objects from that table but first a need to find if in a database called xyz with a bunch of tables I could list all the tables in it, and then I will read the contents of he choosen table from the database. Thank you all!!!
[reply]
Re: Database Table Names
by EdwardG (Vicar) on Nov 08, 2003 at 16:09 UTC
    This is getting a little repetitive

    In SQL Server 2000 you can get a list of table names very simply - 

    select name
from sysobjects
where xtype = 'U'

    [download]

    If you know how to run a SQL query you will have no trouble using this code to get a list of tables.

    For the sake of portability it might also be useful to note that ANSI SQL-92 provides a standard for querying table schema information: 
    select TABLE_NAME from INFORMATION_SCHEMA.TABLES

    [download]

    See also this discussion started by gmax, particularly this node which has more relevant info.

[reply]
[d/l]
[select]
      And, praytell, what level of compliance with ANSI SQL-92 requires INFORMATION_SCHEMA.TABLES to exist, and which databases provide it? Remember that vendors are allowed to declare compliance with the standard when they have only complied with a fairly small subset of the features it specifies.

      For the record, I just grabbed a connection to the first thing that came to hand (Oracle 8) and found that it does not support this. Portability obviously doesn't mean portable to THAT...

      Also please edit your post to use relative URLs rather than absolute ones. See Warn people who have perlmonks in a URL? for more on that.

[reply]
Re: Database Table Names
by hmerrill (Friar) on Nov 07, 2003 at 21:36 UTC
    Is SQL2000 a Microsoft product?? If it is, then do you might be able to use ODBC. As the others have said, if you can use DBI, you should - DBI stands for Database Independent Interface - if you don't have it yet, search on google for "Perl DBI" and I'm sure you'll find it. Two parts to using DBI to access your database: 
      1. DBI
  2. specific DBD module for your particular database, like
       DBD::mysql for the MySQL database

    [download]
    I know there's a DBD::ODBC module that you might be able to use - that's about the best I can do since I don't have experience using Perl or DBI on a Windows platform.

    HTH.
[reply]
[d/l]

Back to Seekers of Perl Wisdom