Sql-server – way to force inheritance of a procedure’s schema to all procedures called within it

schemasql serversql-server-2012stored-procedures

If I have a stored procedure schema1.proc1 and it selects from table1, it correctly selects from schema1.table1. However, when schema1.proc1 executes proc2, it tries to find and execute dbo.proc2.

There is no dbo.proc2 so it returns an error that stored procedure is not found. It never looks for schema1.proc2 even though sys.sysdepends links schema1.proc1 to schema1.proc2. However, there is also a schema2.proc2, but the error returned is just that proc2 is not found or does not exist.

Is there any way other than making that user's default schema = schema1 to have the stored procedure execute the procedure from its own schema (i.e. assume schema1.proc2 should be executed prior to searching for dbo.proc2)?

I know that we could simply preface proc2 with schema1 explicitly, but we are trying to avoid having to find all stored procedure calls within the stored procedures to append the schema prefix of the calling procedure to the procedure being called.

I can see from sys.sysdepends that it correctly links proc1 to the correct database object proc2, that is also associated with schema1, but for some reason it does not seem to look for that proc2, but instead for dbo.proc2.

Please let me know if there is some database level setting that I am just missing to tell the compiler which to check first.

Best Answer

Honestly, the right way to do this is to fix the code. Schema designation should always be explicit. You can use synonyms in the meantime to mask the problem and buy you time:

CREATE SYNONYM dbo.proc2 FOR schema1.proc2;

However that doesn't help if you have two procedures in the database, schema1.proc1 and schema2.proc2, and they both say EXEC proc2, because they're both going to follow the path of the synonym.

The other thing I highly recommend is to not use schemas as a division for storing different objects with the exact same name. I can see the purpose in a development environment, where you want to easily switch schemas to test your code against a QA or test schema, but this should be wholesale, and schema1.Employees and schema2.Employees should not both be valid at the same time - a better way to have this type of independence would be databases rather than schemas (IMHO).

It shouldn't be "quite a chore" to find all the stored procedures that reference other stored procedures without schema references

You can easily find all the objects that are referenced without a schema:

SELECT CallerSchema = caller_sch.name, CallerName = callers.name,
  dep.referenced_entity_name, caller_mod.definition
FROM sys.sql_expression_dependencies AS dep
  INNER JOIN sys.procedures AS callers
  ON callers.[object_id] = dep.[referencing_id]
  INNER JOIN sys.schemas AS caller_sch
  ON callers.schema_id = caller_sch.schema_id
  INNER JOIN sys.sql_modules AS caller_mod
  ON callers.[object_id] = caller_mod.[object_id]
WHERE dep.referenced_schema_name IS NULL;

You can also do this with brute force parsing, though this is a little fuzzier, and assumes your schema names are not common enough to cause false positives (and that the schema isn't referenced anywhere - frankly the above is much more reliable):

SELECT CallerName = callers.name, CallerSchema = caller_sch.name,
    CalleeName = callees.name, CalleeSchema = callee_sch.name, 
    Definition = caller_mod.definition
FROM sys.sql_dependencies AS dep
  INNER JOIN sys.procedures AS callers
  ON callers.[object_id] = dep.[object_id]
  INNER JOIN sys.schemas AS caller_sch
  ON callers.schema_id = caller_sch.schema_id
  INNER JOIN sys.procedures AS callees
  ON callees.[object_id] = dep.referenced_major_id
  INNER JOIN sys.schemas AS callee_sch
  ON callees.schema_id = callee_sch.schema_id
  INNER JOIN sys.sql_modules AS caller_mod
  ON callers.[object_id] = caller_mod.[object_id]
WHERE caller_mod.definition NOT LIKE N'%' + callee_sch.name + N'%';

Also sysdepends (you shouldn't be using this anymore, there are more modern views for this) just reports dependencies. It does not dictate optimizer access path.

Related Question