find the list of tables for a user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

find the list of tables for a user

Author

Topic

naveen3234
Starting Member

2 Posts

Posted - 2015-02-18 : 20:42:18

hai friends,i am new to this forum.

can any body tell me how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user

thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2015-02-19 : 00:32:14

[CODE];with objects_cte as
(
select
o.name,
o.type_desc,
case
when o.principal_id is null then s.principal_id
else o.principal_id
end as principal_id
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
and o.type in ('U') -- 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
cte.name,
cte.type_desc,
dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name = 'YourUser';[/CODE]

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources