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
GO

CREATE SCHEMA JensTestSchema
GO

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′);

GO

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

GO

exec JensTestSchema.usp_test_schemas

GO

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
AS
SELECT id, say FROM dbo.Table1;
RETURN 0

GO

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
http://www.MidnightDBA.com/Jen

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.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/bad-habits-to-kick-avoiding-the-schema-prefix.aspx

  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:







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?

September 13: SQL Saturday Kansas City
November 3-7: PASS Summit, Seattle, WADecember 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

Blog Posts by Category

DBAs@Midnight

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

The best database career advice you’ve never heard!

DBARoadmap.com

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/