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
Add a comment
|
4 Answers
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
Comments
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
Comments
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.
Comments
Try using the stored procedure sp_databases to get the list of all db
