Show all databases in SQL Server 2012

In this article I describe how to show list of all databases stored in SQL Server.
  • 2551

Introduction

In this article I describe how to show list of all databases stored in SQL Server. Sometimes we need to find particular database or we need to see the list of databases stored in SQL Server. To show all the databases, there are following ways to list all the databases.

Show list of all the databases on an instance of SQL Server using SQL Server Management Studio.

  • Open Microsoft SQL Server Management Studio.
  • Connect to database engine. Open object explorer.
  • Click Databases node and expand it. Now see the list of all databases.

Output:

list all databases.jpg

Show list of all the databases on an instance of SQL Server using T-SQL.

SELECT name, database_id,owner_sid, create_date

FROM sys.databases ;

 

Output:

system databases.jpg

Show list of all the databases on an instance of SQL Server using  Sp_msforeachdb Syastem Stored Procedure:

In my previous article I described how to use  Sp_msforeachdb. You can visit from here- sp_MSforeachdb Stored Procedure in SQL Server 2012.  This stored procedure allows you to iterate through all databases to perform a specific task to run a query against all the databases. It executes SQL query against each database associated with current SQL Server instance. Run following code to view list of all databases:

Example 1

declare @command varchar(500)

set @command='select ''?'''

exec sp_MSforeachdb @command

 

Output:

list of  all databases.jpg

Example 2

Print list of databases.

declare @cmd1 varchar(500)

declare @cmd2 varchar(500)

set @cmd1='print ''?'''

set @cmd2='alter database [?] set compatibility_level=100'

exec sp_MSforeachdb @command1=@cmd1, @command2=@cmd2

 

Output:


view all databases.jpg

 

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.