Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SELECT * FROM sys.databases

Posted on 2013-01-16
7
Medium Priority
?
1,275 Views
Last Modified: 2013-01-16
In running the query below, does the resulting list of databases mean that each database on a SQL server has a table which lists the other co-located databases? Or, is this query actually getting data external to the database itself?

SELECT * FROM sys.databases

Thank you!
JohnD
0
Comment
Question by:johndarby
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 38784684
its actually fetching the data from 'sys' views;
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38784689
"Contains one row per database in the instance of Microsoft SQL Server."


http://msdn.microsoft.com/en-us/library/ms178534(v=sql.100).aspx
0
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 400 total points
ID: 38784705
For further reading on the sys.* views, you may also want to check out:

http://msdn.microsoft.com/en-us/library/ms174365
http://msdn.microsoft.com/en-us/library/ms177862

sys.databases isn't actually a table, it's a system view and what you can see in it depends upon your security on the user you're running the query as
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:johndarby
ID: 38784732
Thank you, each! I am feeling a bit dense here...WHERE is the (meta)data stored? In the MASTER DB?
0
 
LVL 1

Author Comment

by:johndarby
ID: 38784775
lcohan, I se your answer, and from whence you quoted the line, but it seems odd that each SQL Server DB would have one row of system data inscribed into each?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 1400 total points
ID: 38784817
Yes is in a master DB table hidded since 2005 and up so they can't be modified anymore. One Row per each database indeed and they are inserted at db creation time. Mode details in select * from sys.database_files for each physical file - just run the query inside each db on that server.
0
 
LVL 1

Author Closing Comment

by:johndarby
ID: 38784916
Thank you for the splendid education! :)
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question