SMO 172.52.0, skips generation of "hidden" tables.

[sdonovanuk]

If you create a table with this:

CREATE TABLE [dbo].[sysblah](
	[name] [sysname] NOT NULL,
	[principal_id] [int] NOT NULL,
	[diagram_id] [int] IDENTITY(1,1) NOT NULL,
	[version] [int] NULL,
	[definition] [varbinary](max) NULL
) 

And then create the extended-property to "hide" it, by making it look like a system-table:

EXEC sys.sp_addextendedproperty @name=N'microsoft_database_tools_support', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sysblah'

If you then use MSDE (or SMO via API), if you script the entire database (i.e. all objects) . . .

Then . . . you'll notice that the resultant script doesn't contain [dbo].[sysblah].

This seems like a bug. The intent is just to hide the table, not treat it as as "system table".

Weirdly, I've just SMO via C#, and even though you include the table in SelectedObjects, it (i) will NOT create the table but (ii) attempts to create the extended-property, which immediately fails because the table does not exist.

Thanks!

Sean

[shueybubbles]

Per the XML definition of IsSystemObject, that EP is intended to mean that it's treated as a system table.

     <property name="IsSystemObject" type="bit" access='Read' cast = 'true'>
 case 
    when {0}.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = {0}.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N'microsoft_database_tools_support') 
        is not null then 1
    else 0
end          
            </property>

I think the only relic feature that uses this EP is database diagrams:

	declare @val int
	select @val = 1
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sysdiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'TABLE', N'sysdiagrams', NULL, NULL
	end
	
	if NOT EXISTS(	select major_id 
					from sys.extended_properties
					where major_id = object_id(N'dbo.sp_upgraddiagrams') and class = 1 and minor_id = 0 and name = N'microsoft_database_tools_support')
	begin
		exec sp_addextendedproperty N'microsoft_database_tools_support', @val, 'SCHEMA', N'dbo', 'PROCEDURE', N'sp_upgraddiagrams', NULL, NULL
	end

If you set SqlScriptPublishModel.AllowSystemObjects to true when you include the URN in SelectedObjects, does it script the object?