CODEWITHSUNDEEP
sql-server
Jerome WAGNER
Jerome WAGNER

Reputation: 22452

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

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

Upvotes: 2

Views: 1671

Answers (4)

Tobias Pirzer
Tobias Pirzer

Reputation: 1025

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

Upvotes: 1

Samiksha
Samiksha

Reputation: 6192

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453910

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

Upvotes: 1

Dustin Laine
Dustin Laine

Reputation: 38543

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.

https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=395.

Upvotes: 0

Related Questions