14 December 2017
14 December 2017

Finding code smells using SQL Prompt: procedures that lack schema-qualification

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified.

PE001 – Schema name for procedure is not specified

Even where you don’t have to qualify the name of a stored procedure, that is whenever the procedure is in your default schema, performance is slightly better if you specify the schema, and it makes the code more intelligible to others, more consistent, and easier to refactor.

The complete name of any schema-based database object consists of up to four identifiers: the server name, database name, schema name, and object name. Only if you are calling a remote stored procedure would you need a fully qualified name consisting of all four identifiers. If you are calling a procedure in another database, you obviously need its database identifier in the name. Within a database, you only need the object name itself so long as the procedure is in the same schema. By specifying the schema, the database engine needs less searching to identify it. Even system stored procedures should be qualified with the ‘sys’ schema name. When creating a stored procedure as well, it is a good habit to always specify the parent schema.

Database object names aren’t unique within a server, only within a schema, therefore we need to add qualifiers when appropriate, such as the server name, database name, or schema name, to ensure that we can identify the procedure we wish to execute, unambiguously. By doing so, we avoid certain bugs, minimize the time the engine spends searching for the procedure, and help ensure that cached query plans for the procedures get reused.


Problems with nonschema-qualified procedures

A nonschema-qualified procedure name will be either a one-part name with no qualifier, or what looks like a two-part name, database and object, with a double-dot between them where the schema name is missing. In such cases, the database engine must search in several places to find the required procedure, in the following order:

  1. First, it searches the sys schema of the current database
  2. Next, the engine searches the users’ default schema, unless the procedure is nested within another one, in which case it will search the schema that contains the outer procedure
  3. Finally, it searches the dbo schema in the current database

Conversely, if we schema-qualify a procedure, simply by prefixing its name with the name of its schema, then the database engine does not have to search several schemas to locate the object, just the one specified.

Nonschema-qualified procedures introduce unexpected ambiguity, which can cause a bug that is hard to resolve. The more nesting of procedure calls takes place, the more chance of error. This ambiguity can also prevent cached query plans being reused.

The best approach is to qualify procedure names and to make sure that, if names are duplicated within a database, it is done intentionally.

Schema qualification and system stored procedures

System stored procedures can be executed from any database, and Microsoft recommends that you qualify them with the sys schema, though nothing will break if you don’t.

Names of system stored procedures always begin with the characters, sp_, standing for ‘special’ and these procedures are stored in the Resource database. They will appear in the sys schema of all user-defined databases in that instance of SQL Server. If you create a procedure in your database with the same name as a system stored procedure, your procedure will never execute no matter how you qualify it with a schema name. The database engine always searches in the sys schema first.

Duplicate procedure names in the same database

Procedures with the same name can coexist in the same database perfectly happily, if they are in separate schemas, and there are legitimate reasons for doing this. For example, you might want different categories of users to execute different code with the same procedure call. In this case, each group of users would need to have a different default schema, which stored their version of the procedure. Users would have EXECUTE permissions only on their own schema, and use ownership-chaining to access the data they need. Users would then need to specify the procedure without the schema to allow SQL Server to choose the stored procedure from the default schema of the user.

If you have duplicate procedure names, then it becomes even more important to schema-qualify any procedure calls that mustn’t be ambiguous! If not, it can introduce almost undetectable bugs that are caused when the wrong synonymous procedure gets called.

How SQL Server deals with procedure calls that aren’t schema-qualified

We can demonstrate how stored procedures are searched by creating several with the same name but in different schemas. Listing 1 simulates having three different users in the database: myself with god-like powers as DBO, and two other users called The_First_User and The_Second_User. We assign each user to different default schemas and give them a different version of our test procedure. We then see which version of the procedures they execute when they make procedure calls that aren’t schema-qualified, and what happens when we delete one.

Listing 1

If executed, this will give the following results:

What do you make of this?

  • When the first user executes the stored procedure, Test_Procedure, it executes the version in MySchema because that is its default schema, and the database engine looked there next after the checking the sys schema.
  • The same happened to the second user, who was assigned to a different version of Test_Procedure that we put in the schema MyOtherSchema.
  • We reverted to me as user, and because my default schema is dbo, I executed the first version of the procedure.
  • Next, we dropped the MySchema.Test_Procedure and the next time The_Second_User executed Test_Procedure without schema qualification, it was the one in dbo that was executed. That happened because if the database engine can’t find the procedure in the user’s default schema it looks in dbo for it.
  • Finally, The_Second_User is in MyOtherSchema, and executes the schema-qualified procedure and the correct version (MySchema.Test_Procedure) is executed.


It is a very good idea to get into the habit of qualifying the names of procedures with their schema. It is not only makes your code more resilient and maintainable, but as Microsoft introduces new features that use schemas, such as auditing mechanisms, you code contains no ambiguities that could cause problems.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Customizing the SQL Prompt built-in snippets: a better 'ata' snippet

    Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane. SQL Prompt also comes with many useful built-in snippets, but sometimes we

  • Article

    SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

    The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() window clause instead, if you need to impose a particular order on rows in the

  • Article

    Misuse of the scalar user-defined function as a constant (PE017)

    Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to return a global constant: Unless you need to use these as constants in computed columns or constraints, it is generally safer

  • Article

    Beyond Formatting: Improving SQL Code using SQL Prompt Actions

    In this article, I’ll discuss how I use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in various subtle but meaningful ways, such as qualifying object names, standardizing the use of aliases, adding semicolons

  • Article

    Choosing Between Table Variables and Temporary Tables

    People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that both have their uses, and it’s easy to find examples where either one is quicker. In this article, I’ll explain the

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly