SMO is incorrectly defaulting the schema-identifier, when it shouldn't be (e.g. on CREATE PROCEDURE).

[sdonovanuk]

Create a database, e.g. TEST1, and create the following stored-procedure:

CREATE PROCEDURE GetServerName AS BEGIN SELECT @@SERVERNAME END  

Note: no schema identifier was provided above.

Now, in something like SSMS (or Azure Database Studio), whatever, query the stored-procedure (sp) and it clearly states that it belongs to dbo/schema_id=1.

Now, to exercise SMO, use SSMS (or whatever) and generate the SQL to CREATE the sp, and you'll get:

CREATE PROCEDURE [dbo].[GetServerName] AS BEGIN SELECT @@SERVERNAME END 

You'll notice that it's added the schema, [dbo].

I think, that this could actually be a bug. I contend that it shouldn't have added the schema.

Why? Well, apparently it makes a difference . . .

Now, create a second database, e.g. TEST2, and apply the generated sp, then compare, either with (i) Azure Data Studio's "Schema Compare" tool, or (ii) DacFx's SchemaComparison (I assume they are the same thing) and . . . it flags a difference: the SQL it generates for TEST1 doesn't have the schema, but the SQL it generates for TEST2 does have the schema.

My question is: how can I configure SMO options (for calling from C#) to only generate the schema-identifier value it was originally provided (or nothing, if that's the case)? With that, I could run DacFx/SchemaComparison and the two objects would be considered identical.

There could be a little debate as to whether the issue is SMO or DacFx/SchemaComparison, but it seems logical the problem originates in SMO.

Note: the same problem applies to views, UDFs, etc.

[shueybubbles]

set ScriptingPreferences.IncludeScripts.SchemaQualify to false. It's settable in the Tools/Options/Sql Server Object Explorer/Scripting UI in SSMS.

It's tricky for sprocs I guess because they are text objects whose bodies show up in sys.sql_modules. Views will have the same issue. The raw text of the CREATE becomes the definition

[sdonovanuk]

Hi @shueybubbles. So, I tried that and here's the problem. With that value (Schema qualify object names) set to false, the following happens:

if I created as CREATE PROCEDURE GetServerName, it creates as CREATE PROCEDURE GetServerName. Great.

However, imagine I create another procedure against a non-dbo schema:

CREATE SCHEMA fish
CREATE PROCEDURE fish.FishGetServerName AS BEGIN SELECT @@SERVERNAME END  

If I script, it comes out as:

CREATE PROCEDURE FishGetServerName AS BEGIN SELECT @@SERVERNAME END

. . . which isn't what I want, because now it's stripped the schema.

So, I think my original argument still stands -- I think it's a bug in SMO. How does one configure SMO to output the schema-identifier as it was originally created?

There are workarounds, I can parse DacFx/SchemaComparison to find the problem cases, but, that's a lot more work.

There's another way to look at this: how does DacFx/SchemaComparison know, that on the source, I didn't specify the schema-name? If DacFx/SchemaComparison can figure it out, I think SMO can figure it out too. If feels like the setting is being stored in SQL Server somewhere, but I don't know where. For example, if you query sys.procedures, it'll say dbo, even if you don't specify dbo. This is an inconsistency.

My original question was around: how can you configure both (i) SMO and (ii) DaxFx/SchemaComparison so that they generate consistent results?

Or, if you create the procedure without a schema-identifier, maybe it's implicit (and documented somewhere?) that it defaults to dbo all the time. If that's the case, the bug is in DacFx/SchemaComparison.

Or, I could possibly run SMO twice to generate the SPs, (i) for SPs where no schema was specified (though I don't know how to find them), and then (ii) for SPs defined with a schema.

[sdonovanuk]

Update: the CREATE PROCEDURE documentation clearly states that a schema is always derived if it's not provided. Therefore, I suspect this is a problem with DacFx/SchemaComparison.