Details on SQL Server dbo and INFORMATION_SCHEMA

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
crazywolf2010Asked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
<<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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.