Schema

Question

Schema

padmakumark26

Mr or Mrs. 500

Points: 513
More actions
December 7, 2016 at 12:59 am

#316607

Is it possible to create sqlserver objects with out schema like Test not like dbo.Test or Function like F_GetData not like dbo.F_getdata.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

padmakumark26 (12/7/2016)
Is it possible to create sqlserver objects with out schema like Test not like dbo.Test or Function like F_GetData not like dbo.F_getdata.

Schemas are a part of SQL Server. You can't create a "Schemaless" object. Items, from a Single server perspective, will always have a 3 part naming convention: [Database].[Schema].[Object].

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

As Thom said, all objects have a schema. However, you can refer to an object without using its schema name if, I think, your login's default schema is the same as the object's schema. I don't recommend doing that, though - you should always use (at least) the two-part convention schemaname.objectname. This makes your code easier to review and understand, makes (marginally) less work for the database engine in resolving the schema name, and helps to protect your code from future changes.

John

Thom A SSC Guru Points: 99126 More actions · Answer 3

John Mitchell-245523 (12/7/2016)
As Thom said, all objects have a schema. However, you can refer to an object without using its schema name if, I think, your login's default schema is the same as the object's schema. I don't recommend doing that, though - you should always use (at least) the two-part convention schemaname.objectname. This makes your code easier to review and understand, makes (marginally) less work for the database engine in resolving the schema name, and helps to protect your code from future changes.
John

Correct. A few examles to help the OP, however, like you said, I don't really recommend not declaring your schema.

--Get data regardless of your current connection and default schema

SELECT *

FROM DBName.dbo.Table;

--When Connected to DBName

USE DBName;

GO

SELECT *

FROM dbo.Table; --Note that DBname has been excluded. 2 part convention therefore dictates to use the current connected database.

--When connected to DBName, and my Default Schema is dbo

SELECT *

FROM Table; --I have now dropped the dbo as well, as my default schema is dbo. Not always ideal.

--Connected to a different database, but when i know that dbo is my default schema in DBName

USE DBName2;

GO

SELECT *

FROM DBName..Table --Note that double dots. you still need both dots there to define 3 part naming, but SQL Server will use your default schema.

--This really isn't recommended in multi schema environments, especially when different users have different defaults.

[/code]

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

padmakumark26 Mr or Mrs. 500 Points: 513 More actions · Answer 4

Thanks All.

Schema prefix is must for function ? Like Select myschema.myfunction('Test')

Thom A SSC Guru Points: 99126 More actions · Answer 5

padmakumark26 (12/7/2016)
Thanks All.
Schema prefix is must for function ? Like Select myschema.myfunction('Test')

Correct; if using a function, the schema must always be declared.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk