3

I can only get data like this format:

SELECT * FROM Abc.ACADEMY

Where Abc is a username, ACADEMY is a table name, and ABC is also my schema name

What I want to be able to write this as:

SELECT * FROM ACADEMY

How can I achieve this?

p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
Ufuk TÜKENMEZ
  • 75
  • 2
  • 6

3 Answers3

3

If you set the user's default schema to [abc] (or whatever it actually is), you won't have to specify that schema name as part of the full table name anymore.

-- before 
SELECT * FROM abc.ACADEMY
GO

ALTER USER userName WITH DEFAULT_SCHEMA = abc
GO

-- after 
SELECT * FROM ACADEMY
GO
p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
  • @UfukTÜKENMEZ What *exactly* did you try? What doesn't work about it? – p.s.w.g Jul 15 '13 at 19:02
  • @UfukTÜKENMEZ Are you actually executing the code as that user *in SQL* (i.e. connected as that User ID in the connection string)? If so, it should work. If you do specify any schema name, it will assume you are looking for tables in the current user's default schema. – p.s.w.g Jul 15 '13 at 19:09
  • Yes i executed the code as that user. It should work to me.. I'm still trying to solve the problem. Thank you for helping. – Ufuk TÜKENMEZ Jul 15 '13 at 19:25
  • @UfukTÜKENMEZ Try `SELECT SCHEMA_NAME()` to verify that the default schema name is set. If everything worked as intended, it should return `abc` (or whatever schema you set). – p.s.w.g Jul 15 '13 at 20:22
0

I solved it !!

Here is the solution:

Changing of Schema owner which has been set before as my user name to "dbo" solved my problem.

http://www.ufuktukenmez.com/wp-content/uploads/2013/07/Adsiz.png

Thank you for helping.

Ufuk TÜKENMEZ
  • 75
  • 2
  • 6
-2

This is how I usually do it

Use ABC

select * from Academy

TEC C
  • 153
  • 1
  • 3
  • 18