[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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
0
bravi
Asked:
bravi
  • 9
  • 8
1 Solution
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now