Toad World® Forums

View Count


#1

I went to TOAD to find out how many views I had created in a certain DB. I needed this information for something that came up with TOAD Data Modeler.

TOAD reported that I had over 400 views(!), which was, indeed, news to me. Especially since I didn’t see anywhere near that many views listed. So, then I clicked on view details. It turns out that the number is including all of the system and information_schema views! The actual number of views, after copying to Excel (which was a problem - more on that later), was more like 52.

So, in the the View Details window, I selected all the cells that had a real (read: non-system) view. I then right-clicked to copy or export (to Excel). In every other grid I have ever used in TOAD, the contect menu allows me to select copy or export. For some reason, this time it didn’t. I could do compares, creates, alters, etc., but no simple Copy Cells or Export Grid. I was able to resolve the problem by clicking on Edit->Copy, which then let me copy the cells. I found the same behaviour in tables, as well.

First, my apologies for putting two (related) items into one post. Here they are, summarized:

  1. View count should only show non-system views. That was highly confusing to me for a short while. This would also make it behave like the table count.
  2. Please add Copy and Export options to the context menu when viewing details of high-level groupings of items, like all tables and all views.

#2

Hi,
To hide all system object please use this option: Database\SQL Server\Show system objects

But please note - they will be hidden from all places (code completion, system databases etc.)

regarding your second request - you can use details view from selected node and use export. I’m afraid this is the only good place to have it.

Hope that will work for you.


#3

Thanks Alexander. I don’t think that is quite what I am looking for.

Item 1: The table count does not include system tables. But the View count does. This is inconsistent. Also, I don’t want to hide all system tables. I just don’t want them in the view count. You can see a picture of what I mean in the attached image. Obviously there are not 375 views in the AdventureWorks DB.

Item 2: As I mentioned, I was able to use the menu to copy the cells (Edit -> Copy). So there is a work-around. It’s just inconsistent with how the grid works in View Details elsewhere. If, for some reason, it’s a major issue to add that functionality to the context menu, then the Edit -> Copy workaround is acceptable.
ViewCountInTOAD.png


#4

I have a ttached a picture showing a count of the tables in the AdventureWorks DB. It also shows all of the views. Clearly, the number of views is way less than the 375 listed in the count.

I have a question about the views: Why do two of them have green glasses, and the rest have blue glasses? The two with green glasses say “SCHEMABINDING” on them when I hover. I’m just curious. I don’t know what that means.

THank you!
ViewsWithGreenGlassesInTOAD.png


#5

Jonathan,
Thank you for the update.

item1:
the reason why you see correct count for table is you do not have system tables in the database, but you do have system views. To not count system views as well as functions and SP turn off the mentioned option.

item2:
Sorry for the misunderstanding from my side. Now I see your request! I’ve added CR108324.

Regarding Schemabinding: http://msdn.microsoft.com/en-us/library/ms187956.aspx


#6

Alexander,
OK - I understand Item 1 now. A little slow, perhaps, but I get it now…

I notice that System Views are stored in a separate folder, and that folder has no count on it. Is it possible to create a “User Views” folder, with it’s own count? Or, perhaps better for the end user, have the view count show only user views, and then the System Views folder show a count of the System Views?

As you mentioned, turning off “Show System Objects” hides all system objects everywhere. This is a bit more drastic than I am looking for. (Although it does fix the count issue… )

Thanks for the Schema Binding link! That was helpful. I was not aware of that option, or what it does. It’s a rather powerful flag, in one little word. “Thou shalt NOT make any changes to tables in a schema-bound view!”


#7

I would like to comment a bit about system tables. As you probably know, Microsoft drastically changed the nature of system tables in SQL Server 2005. As mentioned in BOL, “…Users cannot directly query or update the system tables except through a dedicated administrator connection (DAC) that should be used only under the direction of Microsoft Customer Services…”. Obviously, those tables are not visible under normal connection at all, no matter what setting is set.

From the other hand, there are “pseudo-system” tables that are visible under non-DAC connection and which might be placed in System folder of Tables folder (go to master database to see this). If I remember correctly, Microsoft uses the following criteria for defining “pseudo-system” tables:

CAST(case
when tbl.is_ms_shipped = 1 then 1
when(select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N’‘microsoft_database_tools_support’’) is not null
then 1
else 0
end AS bit) as [IsSystemObject]

Here tbl is an alias for sys.all_objects system view.

As you can see, a user even can change some table to system and vice versa by simply changing its extended property. To make things even more complicated, some tables (e.g., spt_* tables) have sys.all_objects.is_ms_shipped = 1 and sys.all_objects.type = “U” at the same time. And “U” always was a criteria to treat a table as a non-system one.

That is why you can only see a few “pseudo-system” tables in the Object Explorer and a lot of system views at the same time.

Igor.


#8

We can change the behavior to another. But it the current behavior exists for a quite long preiod (seems to me from the begining :slight_smile: I’d like to see what other community members think about this.

---------- The Question:
…perhaps better for the end user, have the view count show only user views, and then the System Views folder show a count of the System Views?


#9

I created a poll for this.