Retrieving a list ot tables

Hello Everyone and thanks for your help in advance. I am working on an application that connects to SQL Server. I need to find out if there is any way (I know there is, not sure how) to retrieve a list of tables within a database and also, a way to retrieve a list of databases within a server. I am using VB.Net in a web application. Any help on this owuld be greatly appreciated.

you can use SQL helper procedures like

sp_tables for tables

and

sp_databases for databases

this is recommended way, you can also try to use system views to get this data but it is not recommended by Microsoft.

Thanks

|||

To retrieve a list of databases...

Select [name]as DBName,crdatefrommaster.dbo.sysdatabaseswherehas_dbaccess([name]) = 1andNOT( (DATABASEPROPERTY([name], N'isdetached') <> 0)or (DATABASEPROPERTY([name], N'isshutdown') <> 0)or (DATABASEPROPERTY([name], N'issuspect') <> 0)or (DATABASEPROPERTY([name], N'isoffline') <> 0)or (DATABASEPROPERTY([name], N'isinload') <> 0)or (DATABASEPROPERTY([name], N'isinrecovery') <> 0)or (DATABASEPROPERTY([name], N'isnotrecovered') <> 0))

And to retrieve the list of tables in database [DatabaseName]...

select table_catalog, table_schema, table_name,sysobjects.crdatefrom information_schema.tablesinnerjoinsysobjectsonsysobjects.type ='U'andsysobjects.name = information_schema.tables.table_namewhere table_type ='BASE TABLE'and table_catalog ='[DatabaseName]'