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

TEST1 と TEST2 の stored-procedure スクリプトで問題を再現し、その後、プロシージャ、ビュー、UDFs に対する SMO の scripting オプションと API の動作を調査します。生成された定義を Schema Compare と比較します。省略されたスキーマが省略されたままになり、同等のオブジェクトが異なるものとして報告されなければ完了です。

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.