SqlServer 08: Query to list all databases in an instance?

Question

How do I list all the databases for a given sql server 08 instance using sqlcmd?

Heinzi · Accepted Answer · 2021-07-23T13:58:05.167

sqlcmd -E -S SERVER\INSTANCE -Q "sp_databases"

Notes:

-E: Use a trusted connection ("Windows authentication"). Replace by -U username -P password for SQL Server authentication.
-S SERVER\INSTANCE: The instance of SQL Server to which to connect. If you don't know the name of your instance, you can use sqlcmd -L to get a list.
-Q: The query to execute. The uppercase Q causes sqlcmd to exit after executing the query.

score 39 · Answer 2 · answered Mar 23 '17 at 21:42

39

To elaborate with more detail for the sqlcmd newbie:

C:\> sqlcmd -S <the_server_name>
1> select name from sys.databases
2> go

answered Mar 23 '17 at 21:42

Shaun Luttin

1

Where do I find out what the server name is? Is there a command to list all the servers? – Felix Eve Aug 28 '18 at 07:48
@FelixEve: https://stackoverflow.com/q/141154/87698 – Heinzi Jul 29 '20 at 07:19

score 32 · Answer 3 · answered Jan 18 '10 at 17:29

32

EXEC sp_databases

or

SELECT NAME FROM sys.sysdatabases

or

EXEC sp_msForEachDB 'PRINT ''?''';

answered Jan 18 '10 at 17:29

D'Arcy Rittich

Up voted for options but accepted Heinzi because it includes the sqlcmd aspect. Thanks! – Dane O'Connor Jan 18 '10 at 17:32
5

I'm a newb and may be stating the obvious here, but I had to do 'go' after the EXEC line like this `1>EXEC sp_databases2>go` – barlop Jul 01 '15 at 20:15

score 3 · Answer 4 · answered Jan 18 '10 at 17:30

3

You can use sp_databases stored procedure.

answered Jan 18 '10 at 17:30

Giorgi

4 Answers4