Solved

Details on SQL Server dbo and INFORMATION_SCHEMA

Posted on 2012-03-22
2
359 Views
Last Modified: 2012-04-10
Hi,
Can someone explain how dbo (database owner) works?  I have most tables under dbo but there are some which are under different user.

Also how can I use INFORMATION_SCHEMA to list details of tables,indexes ?

Thanks
0
Comment
Question by:crazywolf2010
[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
2 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37751716
<<Can someone explain how dbo (database owner) works?>>
There is a historical reason where you must be carefull when talking about dbo. There is the database owner as a role db_owner http://msdn.microsoft.com/en-us/library/ms189121(v=sql.90).aspx
and the dbo schema, the automatic default schema http://msdn.microsoft.com/en-us/library/ms190387.aspx

<<  I have most tables under dbo but there are some which are under different user.>>
Most tables under the schema dbo that's logic because it's the default
and some under a different schema not user, that was the historical part until it changed with version 2005.



<<Also how can I use INFORMATION_SCHEMA to list details of tables,indexes ?>>
Depends on what details you want

select * from informations_chema.tables
Select * from information_schema.columns
More info http://msdn.microsoft.com/en-us/library/ms186778.aspx
But more detailed info tables you can find in sys.tables and sys.objects,  sys.indexes.

You can join them together as much as you want. OBJECT_ID() and OBJECT_NAME() can be helpfull to change from one type to the other.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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