CODEWITHSUNDEEP
sqlsql-serverdatabase
tspga
tspga

Reputation: 117

How to get list of all available databases and tables in them in order to match them with the given in text box?

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.

Upvotes: 0

Views: 2626

Answers (3)

Ben Thul
Ben Thul

Reputation: 32707

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;
   }
}

Upvotes: 2

hashbrown
hashbrown

Reputation: 3516

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)

Upvotes: 1

Eddy
Eddy

Reputation: 235

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.

Upvotes: 0

Related Questions