Determine Table Owner w/Catalog View

18 Aug 2006 3:58 PM

Dan

I am using the sys.tables catalog view to get information about tables in my
database. I would like to also determine the owner of the table. Is there a
catalog view that contains this information?

18 Aug 2006 4:11 PM

Aaron Bertrand [SQL Server MVP]

In SQL Server 2005 there is not really the concept of an "owner" this has
been shifted to the concept of a "schema"... the schema has an owner,
however. This might help track down what you're looking for:

SELECT

[TABLE] = t.name,

[SCHEMA] = s.name,

[SCHEMA_OWNER] = p.name

FROM sys.tables t

INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id

INNER JOIN sys.server_principals p

ON s.principal_id = p.principal_id

ORDER BY 1;

Show quote

"Dan" <D**@discussions.microsoft.com> wrote in message
news:12E59A41-9612-4479-A372-9483ED3E9406@microsoft.com...
>I am using the sys.tables catalog view to get information about tables in
>my
> database. I would like to also determine the owner of the table. Is there
> a
> catalog view that contains this information?
>

18 Aug 2006 4:20 PM

amish

Aaron Bertrand [SQL Server MVP] wrote:

Show quote

> In SQL Server 2005 there is not really the concept of an "owner" this has
> been shifted to the concept of a "schema"... the schema has an owner,
> however. This might help track down what you're looking for:
>
>
> SELECT
>
> [TABLE] = t.name,
>
> [SCHEMA] = s.name,
>
> [SCHEMA_OWNER] = p.name
>
> FROM sys.tables t
>
> INNER JOIN sys.schemas s
>
> ON t.schema_id = s.schema_id
>
> INNER JOIN sys.server_principals p
>
> ON s.principal_id = p.principal_id
>
> ORDER BY 1;
>
>
>
>
>
>
>
>
>
> "Dan" <D**@discussions.microsoft.com> wrote in message
> news:12E59A41-9612-4479-A372-9483ED3E9406@microsoft.com...
> >I am using the sys.tables catalog view to get information about tables in
> >my
> > database. I would like to also determine the owner of the table. Is there
> > a
> > catalog view that contains this information?
> >

You can use Information_schema.tables view also to get this
information.

Regards
Amish Shah
http://shahamishm.tripod.com

18 Aug 2006 4:45 PM

Dan

Perfect. Thanks!

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> In SQL Server 2005 there is not really the concept of an "owner" this has
> been shifted to the concept of a "schema"... the schema has an owner,
> however. This might help track down what you're looking for:
>
>
> SELECT
>
> [TABLE] = t.name,
>
> [SCHEMA] = s.name,
>
> [SCHEMA_OWNER] = p.name
>
> FROM sys.tables t
>
> INNER JOIN sys.schemas s
>
> ON t.schema_id = s.schema_id
>
> INNER JOIN sys.server_principals p
>
> ON s.principal_id = p.principal_id
>
> ORDER BY 1;
>
>
>
>
>
>
>
>
>
> "Dan" <D**@discussions.microsoft.com> wrote in message
> news:12E59A41-9612-4479-A372-9483ED3E9406@microsoft.com...
> >I am using the sys.tables catalog view to get information about tables in
> >my
> > database. I would like to also determine the owner of the table. Is there
> > a
> > catalog view that contains this information?
> >
>
>
>