List all the database on SQL Servers.
All the following Stored Procedure list all the Databases on Server.
I personally use EXEC sp_databases because it gives the same results as other but it is self explaining.
----SQL SERVER 2005 System Procedures
EXEC sp_databases
EXEC sp_helpdb
----SQL 2000 Method still works in SQL Server 2005
SELECT name
FROM sys.databases
SELECT name
FROM sys.sysdatabases
----SQL SERVER Un-Documented Procedure
EXEC sp_msForEachDB 'PRINT ''?'''
Reference : Pinal Dave (https://blog.sqlauthority.com)
64 Comments. Leave new
Nice
Depending on the user’s role and database membership, these various techniques will not return the same result and may even return nothing.
sp_databases requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission, otherwise it returns an empty result — even for those databases where you are a member.
sp_helpdb and EXEC sp_msForEachDB ‘PRINT ”?”’ return those database for which you are a member, even if you don’t have any of the above permissions.
sys.databases and sys.sysdatabases return all databases regardless of permissons or membership.
Could someone tell me code to list all sql instant name in a computer?
The code above is t-sql and must be connect to get server instant name. I mean list all instant name of sql server without connect to any database.
Hi all,
I have one SQL2005 server and SQL 2008 server. We are in migration process for both the servers. Now my task is to take down the list of all the SQL 2005 and 2008 databases, their tables and all other details. And create those databases and tables onto new server and restore the same.
Please assist me a script how do I achieve that. I am not a SQL expert. So looking for your guidance.
Thank you
thanks its a valid information
When i run command Select databasepropertyex (dbname, status), it shows me database status “Down”. But when i check the database status by using GUI (Righ click on the database, select the property), it shows me “Normal”.
I check everything, sql error log but not getting any error information. Can you please help me to resolve the issue?
Thanks – Dharmendra
thanks a lot…….
Thanks Pinaldave… it’s really helpful for ME…. :)
How can I create a list of only system database in SQL server 2008 R2 and 2012?
I don’t know how many database I have, so that I want to see my all databases and tables. what is that commend in SQL?
Hi Pinal Dave.
Thanks for this goldmine:-)
1) EXEC sp_databases and EXEC sp_helpdb is very slow 1m20s on my server
(Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86) Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.1 (Build 7601: Service Pack 1) )
with 140 databases. Do you have any idea of the ​​reason?
Regards Anders.
Hi Pinal,
If a database is online that signifies that for this databases server is primary…?
and is there any query to get list of all database for which this server is secondary..?
I am fetching the list of database on my SQL server (SQL Express 2005).
Instead of using following as suggested by Pinal
1. EXEC sp_databases
2. SELECT name FROM sys.databases
I am establishing the connection with SQL Express 2005 as
strUserCon = “data source = ServerName; initial catalog = Users; user id = uid; password = pwd;”
and I am still getting all the databases listed.
I wonder how initial catalog = Users works when initial catalog is supposed to be database name.
Could you please explain ?
What are the different kind of database server?
Hi Pinal Dave,
When I compared these query execution,
The following are faster to get result.
SELECT name
FROM sys.databases
SELECT name
FROM sys.sysdatabases
But,
the following are slower than the first two quires.
EXEC sp_databases
EXEC sp_helpdb
EXEC sp_msForEachDB ‘PRINT ”?”
Why EXEC sp_ … is slower. What is the reason behind it ?
Thanks in advance….