Hi all

My software exports the tables and views of the database catalog and
tries to exclude system tables. Therefore it checks the table type.

If you click on the menu "Database Diagrams - New Database Diagram" in
the "SQL Server Management Studio Express", you'll be ask to create
support objects (the table sysdiagrams): "This database does not have
one or more of the support objects required to use database diagramming.
Do you wish to create them?"
You can add tables in a new dialog after answering with yes (or just
close the dialog). Afterwards there is the table sysdiagrams under the
menu "Tables - System Tables".

The sysdiagrams table is marked as user table:
select * from sys.tables where name like 'sys%'
sysdiagrams    1803153469    NULL    1    0    U     USER_TABLE    2006-11-02 17:22:39.550
2006-11-02 17:22:39.583    0    0    0    1    NULL    5    0    1    0    0    0    0    0    0    0

Other system tables are marked as system (i.e. sysallocunits).

Is it a bug in the Management Studio ?

Microsoft SQL Server Management Studio Express                        9.00.2047.00
Microsoft Data Access Components (MDAC)                        2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML                        2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                        7.0.5730.11
Microsoft .NET Framework                        2.0.50727.42
Operating System                        5.1.2600

Thank you in advance
Daniel

Daniel,
SSMS is able to distinguish it as a "pseudo system table" due to an extended
property added for this purpose, which you could also use.
     Cheers,
          Paul Ibison SQL Server MVP, www.replicationanswers.com .

To wit, as long as you don't mark any of your own objects with the same
extended properties:


SELECT o.*
    FROM sys.objects o
    LEFT OUTER JOIN
        sys.extended_properties ep
    ON o.[object_id] = ep.major_id
        AND ep.class_desc = 'OBJECT_OR_COLUMN'
        AND ep.[name] = 'microsoft_database_tools_support'
    WHERE
        o.is_ms_shipped = 0
        AND ep.major_id IS NULL;


Personally, I think that the diagram objects should be marked as
is_ms_shipped = 1, even though they are not installed by default.



Show quoteHide quote

I agree...

Show quoteHide quote

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
I completely agree with the sentiment. I guess the problem is that Mgmt
Studio is just another user client without access to secret hooks, and
thus it does not have access to that flag.

But I think that is a limitation that Microsoft should find a solution
for.

Sounds like this should be put on connect...



Show quoteHide quote

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:
OK.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=235466

Utility for Table Schemas
Table Changes
Query Analyser - output SQL commands to Results pane
Syntax coloring: SERVICE
repost: SqlServer 2005 => SMS => Object Explorer => Programability Filter question