0

I want to validate the database and table name given in text box with the list of available database and table names on server. Is there any way so that I can retrieve the list of available database and table names, so that I can match them with the one given in text box.

Example - In text box I give [anyDatabaseName].[dbo].[anyTableName] with the list of available database and table names in same format.

I have databases like [database1] and has tables like [database1Table1], [database1Table2], [database1Table3], so on. Similarly [database2] has tables like [database2Table1], [database2Table2], [database2Table3], so on and I want to match database and table name given on whole in text box with the list available on server.

UPDATE: this has helped me little

but I don't want system databases and I also want ids of databases, I will be gathering all information and then inserting that info in single table.

Community
  • 1
  • 1
tspga
  • 117
  • 1
  • 13
  • You can get this information from the information schema tables: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html – Cyclonecode Nov 25 '14 at 07:00

3 Answers3

2

Use SMO. I'll be using powershell as my demonstration language, but what I'm doing here should be immediately transferable to C# (or whatever .NET language you're using).

import-module sqlps -disablenamechecking;
$srv = new-object microsoft.sqlserver.management.smo.server '.';
foreach ($db in $srv.databases) {
   foreach ($tbl in $db.tables) {
      $tbl | select parent, name;
   }
}
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
1

Ok - this heavily depends on the database you are using. Let's just assume that both Database1 and Database2 are MySQL databases.

In MySQL, you can query information_schema.tables to generate a list of tables in that database. So for example, through your PHP program, you can fire this query -

SELECT '[' + DB_NAME() + '].[' + table_schema + '].[' table_name + ']' table_name from Information_schema.tables

This will generate a list of tables available under each database like below:

[database1].[schema].[table1]
[database1].[schema].[table2]
....

Once you get this list, it's a matter of searching your input in this list.

If you use other databases (e.g. Oracle), the SELECT query will be different (you need to query db_tables)

hashbrown
  • 3,438
  • 1
  • 19
  • 37
  • I am using sql server and concat is not available. @hashbrown – tspga Nov 25 '14 at 07:20
  • `Concat` is available for SQL Server see this - http://msdn.microsoft.com/en-us/library/hh231515.aspx – hashbrown Nov 25 '14 at 07:27
  • Alternatively you can use `+`. The concat part is not that important for your question. I showed you the basic idea about retrieving schema.tables here. If you think the answer is helpful, you may upvote – hashbrown Nov 25 '14 at 07:29
  • I have used this query SELECT table_schema +'.'+ table_name from Information_schema.tables the results are [dbo].[TableNames] but I want result like [databaseNames].[dbo].[tableNames] @hashbrown – tspga Nov 25 '14 at 07:53
  • Updated my answer above. Check the new query I gave there. It will give the result in your desired format – hashbrown Nov 25 '14 at 09:47
  • The function `DB_NAME()` basically returns the database name – hashbrown Nov 25 '14 at 09:47
  • DB_NAME() returns database name yes, but it returns the current db name only while I want all database list available on my server @hashbrown . – tspga Nov 27 '14 at 07:57
  • There is no reliable way through which you can obtain the list of all the available database(s) from a server. You can retrieve available schema /user etc. against a known `DSN`. Having said that, Ben Thul's reply above shows you a possible technique to retrieve database names using SQL Server Management Object. You may want to try that. – hashbrown Nov 27 '14 at 12:19
0

The SQL statement to get a list of the databases in MySQL is 'SHOW DATABASES'.

In PHP this would be something like:

$result = mysqli_query($connection, "SHOW DATABASES");

$db_list = array();

while($db = mysql_fetch_row($db_list)) {
   $db_list[] = $db[0];
}

Then you can loop through this databases and retrieve the tables for each database.

Eddy
  • 225
  • 2
  • 7