Home » Beginner »sql server »sqlserverpedia-syndication »SSC »Tips »TSQL » Currently Reading:

Tip: Schema Qualify Objects in SPs

December 6, 2010 Beginner, sql server, sqlserverpedia-syndication, SSC, Tips, TSQL 4 Comments

“Eat your broccoli.”

“Wear your gloves.”

“Schema qualify your objects.”

Your Mom wasn’t kidding, and she always gave the best advice. We’ve already seen how it would have paid off for us to end all T-SQL statements with a semicolon, even though it’s not currently required – after all, don’t CTEs and MERGE statements have semicolon requirements?  But it was always just easier not to bother with typing the schema with every object name. Easier, but not better.

SQL Server 2008 R2 Books Online says:

“Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not schema-qualified default to the schema of the stored procedure.”

Well, let’s just test that, shall we? This script creates a schema, two tables (one with the new schema, one with the default schema), and a stored procedure. The SP selects data from a non-schema-qualified table.

USE mydb


CREATE TABLE JensTestSchema.Table1 (id TINYINT, say VARCHAR(100));
CREATE TABLE dbo.Table1 (id TINYINT, say VARCHAR(100));

INSERT INTO JensTestSchema.Table1 (id, say) VALUES (1, ‘Selected from JensTestSchema.Table1′);
INSERT INTO dbo.Table1 (id, say) VALUES (1, ‘Selected from dbo.Table1′);


CREATE PROCEDURE JensTestSchema.usp_test_schemas
SELECT id, say FROM Table1;


exec JensTestSchema.usp_test_schemas


What did we get?  “1 Selected from JensTestSchema.Table1″, of course! We didn’t specify a schema, so the SELECT pulled data from the object with the same schema as the stored procedure. 

Now let’s qualify that table object:

-- Now alter the SP so that the internally referenced table is schema qualified.
ALTER PROCEDURE JensTestSchema.usp_test_schemas
SELECT id, say FROM dbo.Table1;


exec JensTestSchema.usp_test_schemas

Aaaand we get “1 Selected from dbo.Table1″.  

Point proven. Now maybe you’ll listen to your mom, won’t you?

One more excellent reason to follow good coding practices: BOL says something else about unspecified schemas in SPs:

…access to those tables through the stored procedure is restricted by default to the creator of the procedure.

There’s nothing like a good, obscure permissions problem to really raise the blood pressure!

Happy days,
Jen McCown

Currently there are "4 comments" on this Article:

  1. [...] This post was mentioned on Twitter by Jen & Sean McCown, Jen & Sean McCown. Jen & Sean McCown said: Blog: Tip: Schema Qualify Objects in SPs: “Eat your broccoli.” “Wear your gloves.” “Schema qualify your objects…. http://bit.ly/dLDhwW [...]

  2. I talked about this in my bad habits series last year. Was not aware of the R2 doc at the time.


  3. [...] Tip: Schema Qualify Objects in SPs – Need another dose of awesomesauce this week? Always! Jen McCown, 1 half of the MidnightDBA’s, shares another important tip with us this week and be sure to check out her brilliant interview of Jes Borland(@grrl_geek) too. [...]

Comment on this Article:

Minion Reindex by MidnightDBA is here!


Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/