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)
Thanks Buddy
really its good. thx………
nice!
its very nice
thankyou very much
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’.
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
it’s very nice thak you very much
Really it shows the diffrenece between sys.sysdatabases and sys.databses
Thanks to pinal dave
Harinath
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.
Thanks!!!!
very good
Very good!
How to get all SQL datatype via SQL query?
Thnaks!
select name from sys.types
Hi Dave,
Can you find a database from all the sql servers in a domain? Good if you can help.
@ Tim
This script will return all SQL Servers in a network.
http://blog.sqlauthority.com/2007/04/13/sql-server-script-to-find-sql-server-on-network/
or
http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
Regards,
IM
[...] SQL SERVER – 2005 – List all the database [...]
Thank you very much
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
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.
how can i insert a new column in existing table?
but that column should insert between two columns
plz tell me query
thank u
how can i get list of user defined databases
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
thanks dear
thanks dear for sysdatabase
how can i get list of user defined databases?
@Sachin,
sys.sysdatabases will list out all databases, includes System and user defined, both.
Thanks,
Tejas
Hi, how to use these commands and join them with a table in a database?
can´t find anything, thanks for help
michael
Thanks, Its really very helpful.
In version 2000, the query should be
select * from master..sysdatabases
Thank you so much. Its very helpful,Greetings from Turkey
How to check all database disk space uitilized on a certain sql server?
exec sp_databases
will show you occupied disk space
how can i insert a new column in existing table?
but that column should insert between two columns
plz tell me query
thank u
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
Thanks, Great.
marvellous….thanx dear…
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
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
They should be
SELECT * FROM master..sysdatabases
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
Use this
select
name
,DATABASEPROPERTYEX(name, ‘Status’)
,crdate
,filename
,”
,*
from master..sysdatabases
where DATABASEPROPERTYEX(name, ‘Status’)=’ONLINE’
Hi Pinal,
Just a query what does version ‘Null’ signifies while querying sysdatabases.
Also do let us know about status ’4259841′, ststus2 ’1627389952′.
Thanx
Read about SYSDATABASES in SQL Server help file
It has informations about all the columns
Thanx Buddy :)
Thanks a bunch, it helps me a lot.
Hi..
Can you tell me the query from which I can retrive all the records from a database in sql server 2005 or 2008.
What do you mean by all records from a database?
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
Useful post, ty :)
Great !!!!! thanks a lot !!!now my sqlcmd it’s more than a weapon it’s one great soldier of work yes!!
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?
[…] List all the database A Simple script which list all the database from the 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….