How to include the schema when scripting SQL Server stored procedures?

Question

I am trying to force SMO to schema qualify object names for stored procedures, UDFs and views, but for some reason it doesn't work. I have got a stored procedure without a schema and need to have the default schema pre-pended to it:

I have this code:

var procs = from sp in _smoDB.StoredProcedures.OfType<StoredProcedure>()
              where !sp.IsSystemObject && !sp.IsEncrypted
              select sp;

foreach ( StoredProcedure sproc in procs ) {

    var script = sproc.Script( ScriptOption.SchemaQualify );
    var scriptText = script[3];

}

When I access the script text it isn't putting the schema name (dbo) in front of the stored prcocedure. Can anyone explain this?

eric · Accepted Answer · 2011-12-10 13:02:52Z

4

I haven't worked with SMO in 2008/R2 (only 2005), so I may be missing something, but here's what's I'd try:

var procs = from sp in _smoDB.StoredProcedures.OfType<StoredProcedure>()
            where !sp.IsSystemObject && !sp.IsEncrypted               
            select sp; 

var options = new ScriptOptions();         // reusable ScriptOptions object
options.SchemaQualify = true;
options.EnforceScriptingOptions = true;    // surprise!
foreach ( StoredProcedure sproc in procs ) 
{
    var script = sproc.Script( options );     
    var scriptText = script[3];  
}

(I haven't tested this)

I'm guessing the stored procedure was originally created without specifying the schema, and that is what is being scripted. To get a schema-qualified script, you'll need to enforce the scripting options and will lose any comments and formatting (here's the article from MSDN).

I believe this will apply to Views and Functions as well.

answered Dec 10, 2011 at 13:02

eric

3913 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Intrepid Over a year ago

Setting the .EnforceScriptingOptons = True scripting option has done the trick. Thanks for this!

Collectives™ on Stack Overflow

How to include the schema when scripting SQL Server stored procedures?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related