SQL SERVER – 2005 – List all the database

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 (http://blog.SQLAuthority.com)

About these ads

66 thoughts on “SQL SERVER – 2005 – List all the database

  1. I think for SQL 2000 you need to do:
    select name from master..sysdatabases

    The select method using sys.databases returns an error:
    Msg 208, Level 16, State 1, Line 1

    Invalid object name ‘sys.sysdatabases’.

    Like

  2. yes i agree with the point of Mr.Josh Davis

    The select method using sys.databases returns an error:
    Msg 208, Level 16, State 1, Line 1

    Invalid object name ’sys.sysdatabases’.

    but the remaining thins are working good

    Like

  3. pinaldrive thank you. . . the database list is just what I needed and thefirst hit Google came up with. Please keep creating stuff and leaving it on the web to benefit others.

    Like

  4. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  5. Hi pinal

    what is the use of sp_help in sql server 2005. when i execute it gives list of proc, usertables , views, extended proc

    Like

  6. How to query or list all online database and offline database? I try “SELECT name FROM sys.databases’ but it shows all database including the offline. Thank you.

    Like

  7. how can i insert a new column in existing table?
    but that column should insert between two columns
    plz tell me query

    thank u

    Like

  8. WHen using

    EXEC sp_databases
    EXEC sp_helpdb

    You can’t find offline databases, using

    SELECT name
    FROM sys.sysdatabases

    You will find all databases even in suspect mode.

    Regards

    Like

  9. how can i insert a new column in existing table?
    but that column should insert between two columns
    plz tell me query

    thank u

    Like

    • The ordinal position of the column doesn’t matter as long as you use them in the proper place in the SELECT statement. If you still need it do it via Management studio

      Like

  10. list of used databases since last reboot

    select a.name, a.dbid, max(last_user_seek), max(last_user_scan)
    from sys.sysdatabases a
    left outer join sys.dm_db_index_usage_stats b on a.dbid = b.database_id
    group by a.name, a.dbid

    Like

  11. Hi Dave,

    When I run:

    SELECT * FROM master..sys.databases
    SELECT * FROM master..sys.sysdatabases

    The output is:

    Could not find server ‘master’ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    Execute:

    exec sp_addlinkedserver

    Returns:

    Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
    The server ” already exists.

    Thanks in advance

    Like

  12. Well Dave, Just I want to know what databases are online in sql server 2000, so the next query is ok, for this?

    select
    name
    ,DATABASEPROPERTYEX(name, ‘Status’)
    ,crdate
    ,filename
    ,”
    ,*
    from master..sysdatabases

    Like

    • Use this

      select
      name
      ,DATABASEPROPERTYEX(name, ‘Status’)
      ,crdate
      ,filename
      ,”
      ,*
      from master..sysdatabases
      where DATABASEPROPERTYEX(name, ‘Status’)=’ONLINE’

      Like

  13. Hi Pinal,

    Just a query what does version ‘Null’ signifies while querying sysdatabases.

    Also do let us know about status ‘4259841’, ststus2 ‘1627389952’.

    Thanx

    Like

  14. Here’s a nice script, which will list the databases on your SQL Server, along with the path+filenames of their log & data files.

    SELECT db.database_id,
    db.[name],
    case when mf.type_desc = ‘ROWS’ then ‘Data file’ else ‘Log file’ end,
    mf.physical_name as ‘Filename’
    FROM sys.databases db,
    sys.master_files mf
    where mf.database_id = db.database_id
    order by 2

    Like

  15. 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.

    Like

  16. 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

    Like

  17. 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

    Like

  18. 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.

    Like

  19. 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..?

    Like

  20. 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 ?

    Like

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  22. 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….

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s