Show all databases in SQL Server 2012
In this article I describe how to show list of all databases stored in SQL Server.
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:
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:
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:
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: