How to get list of all view created by a user

How can I get a list of all the views created by a particular in SQL Server. ADOX views object doen't work properly with SQL oledb provider. I cannot use SQL-DMO due to licence problem.
How can I get just the names of all view created by user by querying the database
braviAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select * from sysobjects
where xtype = 'V'
and uid = (select id from sysusers where <whatever> )

CHeers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select * from sysobjects
where xtype = 'V'
and uid = (select id from sysusers where <whatever> )

CHeers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry for the double post...

or

select * from INFORMATION_SCHEMA.views
where TABLE_SCHEMA = 'dbo'

CHeers
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
braviAuthor Commented:
I don't need any system views, only user views are required
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What about these queries:

select * from sysobjects
where xtype = 'V'
and uid > 1

or

select * from INFORMATION_SCHEMA.views
where TABLE_SCHEMA <> 'dbo'

Cheers
0
 
braviAuthor Commented:
I don't need any system views, only user views are required
0
 
braviAuthor Commented:
I need all user views created by a paricular user. What if a particular user has dbo privilege? I won't get even the user created views in that case
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You are right, there is no indication WHO did actually create the table, only to which user the tables are assigned...
0
 
braviAuthor Commented:
I need all user views created by a paricular user. What if a particular user has dbo privilege? I won't get even the user created views in that case
0
 
braviAuthor Commented:
There should be some way to filter out this information, enterprise manager displays the type of view also (system or user).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In fact, in the systems table there is a column that indicates if the views are build by the system, but not by which user they are created. Take the following example:

You have USERA and USERB, both with dbo permissions...

USERA connects to the server, and executes the following script:
CREATE VIEW USERB.MyViewA
AS SELECT ...
GO
CREATE VIEW USERA.MyViewB
AS SELECT ...
GO
CREATE VIEW MyViewC
AS SELECT ...

and USERB connects, and executes a similar script:
CREATE VIEW USERB.MyView1
AS SELECT ...
GO
CREATE VIEW USERA.MyView2
AS SELECT ...
GO
CREATE VIEW MyView3
AS SELECT ...


If you look at EM, you will have 6 views:
USERB.MyViewA
USERA.MyViewB
USERA.MyViewC
USERB.MyView1
USERA.MyView2
USERB.MyView3

BUT not all the views that are owned by UserA are actually created by UserA (and vice-versa). All the views have the bit "systemobject" set to false, but there is no indication to which user actually create it...

Cheers


0
 
braviAuthor Commented:
Can you tell me where this systemobject flag can be found, even that would be useful to me. Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
using the sysobjects, the status column > 0 ...
CHeers
0
 
braviAuthor Commented:
Can you tell me where this systemobject flag can be found, even that would be useful to me. Thanks
0
 
braviAuthor Commented:
Is it reliable? Books online says its reserved for internal use only. Anyway thanks, I will accept the answer
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You are right, it's internal use only... and the values can differ for sometimes no obvious reason.
For views however, i am sure it can be used reliable way.
Cheers
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
braviAuthor Commented:
Is it reliable? Books online says its reserved for internal use only. Anyway thanks, I will accept the answer
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.