SQL request to list all databases in an instance of Sql-Server ?

Question

Hello,

Is there a way to list all of the existing databases in an instance of Sql Server via a SQL request ?

More generally can I use SQL to fully read the schema of the databases (tables, columns, ..) ?

Thank you

Jerome Wagner

Answer 1

Yes, Sp_msforeachdb, there is also a sp_msforeachtable. You can use both to iteratively retrieve all tables in all DB's and get what you want.

http://blogs.techrepublic.com.com/datacenter/?p=395.

Answer 2

Yes. See the Information Schema Views and sys.databases.

Here's a script for table definitions that may be useful. http://www.builderau.com.au/program/sqlserver/soa/Script-Table-definitions-using-TSQL/0,339028455,339293405,00.htm

Answer 3

Try using the stored procedure sp_databases to get the list of all db

Answer 4

You can get a lot of infos by the following queries:

SELECT * FROM sys.databases

use Northwind

select * from sys.objects where type_desc = 'USER_TABLE'

SELECT t1.name [table], t2.* 
FROM sys.objects t1
        inner join sys.columns t2 on t1.object_id = t2.object_id 
where type_desc = 'USER_TABLE'

sp_help 'Customers' -- Customers = tablename