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
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!!