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?

Shun

Answer 1

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.

Electrophoresis

Answer 2

To elaborate with more detail for the sqlcmd newbie:

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

Autohypnosis

Where do I find out what the server name is? Is there a command to list all the servers? – Messeigneurs

@FelixEve: https://mcmap.net/q/109130/-how-can-i-determine-installed-sql-server-instances-and-their-versions/87698 – Electrophoresis

Answer 3

EXEC sp_databases

or

SELECT NAME FROM sys.sysdatabases

or

EXEC sp_msForEachDB 'PRINT ''?''';

Leaving

Up voted for options but accepted Heinzi because it includes the sqlcmd aspect. Thanks! – Featured

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_databases<ENTER>2>go<ENTER> – Mikaelamikal

Answer 4

You can use sp_databases stored procedure.

Katrinka