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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Fix : Error : Msg 6263, Level 16, State 1, Line 2 Enabling SQL Server 2005 for CLR Support
Next Post
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

Related Posts

64 Comments. Leave new

  • sachin sharma
    October 28, 2009 1:55 pm

    thanks dear

    Reply
  • sachin sharma burhanpur
    October 28, 2009 1:56 pm

    thanks dear for sysdatabase

    how can i get list of user defined databases?

    Reply
  • @Sachin,

    sys.sysdatabases will list out all databases, includes System and user defined, both.

    Thanks,

    Tejas

    Reply
  • Hi, how to use these commands and join them with a table in a database?
    can´t find anything, thanks for help

    michael

    Reply
  • Thanks, Its really very helpful.

    Reply
  • In version 2000, the query should be

    select * from master..sysdatabases

    Reply
  • Thank you so much. Its very helpful,Greetings from Turkey

    Reply
  • How to check all database disk space uitilized on a certain sql server?

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

    thank u

    Reply
    • 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

      Reply
  • Thanks, Great.

    Reply
  • marvellous….thanx dear…

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
    • Use this

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

      Reply
  • Hi Pinal,

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

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

    Thanx

    Reply
    • Read about SYSDATABASES in SQL Server help file
      It has informations about all the columns

      Reply
  • Thanx Buddy :)

    Reply
  • Thanks a bunch, it helps me a lot.

    Reply
  • Hi..
    Can you tell me the query from which I can retrive all the records from a database in sql server 2005 or 2008.

    Reply
  • Mike Gledhill
    January 18, 2011 2:39 pm

    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

    Reply
  • Useful post, ty :)

    Reply

Leave a Reply Cancel reply

Exit mobile version