when to use fully qualified names ([database].[schema].object)

**fireball** · Dec 29 '06, 04:35 PM

Re: when to use fully qualified names ([database].[schema].object)

please, how to specify database name in schema operation like:
select * from sys.schemas where name = <my-schema...
create/drop schema ...
...
?

**John Bell** · Dec 29 '06, 06:15 PM

Re: when to use fully qualified names ([database].[schema].object)

Hi

"fireball" <fireball@onet. kropka.euwrote in message
news:en3dv5$6ce $1@atlantis.new s.tpi.pl...

I wihsh to discuss whether to use fully qualified names:
[database].[schema].object
of objects to operate (create, query..) on is good or not?
>

In general having at least two part names will reduce the need to work out
the schema and therefore be more efficient. Therefore using two part names
in stored procedure and other code is a good idea.

If someone change order of sql code blocks in my script - this may cause
lose of it's context (like: use master / use <mydb>..). I wish to have my
sript independed on changes like this and always produce correct result.
>

This is assuming that the database name does not change! It may be better
just to organise the scripts so you have separate scripts for each database
and then you only need to worry about setting the database once when making
the connection (-d parameter for osql or SQLCMD ) and there would be no USE
statements at all.

Does using full name make use of 'use <db>' statement unnecessary?
>

John

**John Bell** · Dec 29 '06, 06:25 PM

Re: when to use fully qualified names ([database].[schema].object)

Hi

"fireball" <fireball@onet. kropka.euwrote in message
news:en3fhp$bv2 $1@atlantis.new s.tpi.pl...

please, how to specify database name in schema operation like:
select * from sys.schemas where name = <my-schema...
create/drop schema ...
...
?

Have you tried using the scripting options for Management Studio's Object
Explorer to create a script to do this?

For the dropping a schema, right click the schema, choose Script schema
as... and then Drop or Create to a window or clipboard. The drop option will
create a script like:
USE [MyDb]

GO

/****** Object: Schema [MySchema] Script Date: 12/29/2006 18:07:59 ******/

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'MySchema')

DROP SCHEMA [MySchema]

The create option will give you something like:

USE [MyDB]

GO

/****** Object: Schema [MySchema] Script Date: 12/29/2006 18:09:47 ******/

CREATE SCHEMA [MySchema] AUTHORIZATION [MySchema]

You can remove the unnecessary USE statements and comments.

John

**Erland Sommarskog** · Dec 29 '06, 10:35 PM

Re: when to use fully qualified names ([database].[schema].object)

fireball (fireball@onet. kropka.eu) writes:

I wihsh to discuss whether to use fully qualified names:
[database].[schema].object
of objects to operate (create, query..) on is good or not?

Use two-part names, not three-part names.

Two-part names are particulary important in SQL code outside stored
procedures. If user Joe submits this query:

SELECT col1 FROM dbo.tbl WHERE x = 132

and then user Czeslaw submits this query:

SELECT col1 FROM dbo.tbl WHERE x = 34

the query-plan will be reused (assuming auto-parameterisatio n). But if
"dbo." is not there, Joe and Czeslaw cannot share plans, because all of
a suddent there may be a table Joe.tbl.

This is a little different on SQL 2005 where users can have a default
schema which does not agree with their username, for instance "dbo".

Within stored procedures, it should not have any difference as far
as I can see, but I have heard people from Microsoft say that it has.

For the database, relies on the current database, unless you are running
cross-database queries. Including the database name, makes life difficult
when you want to run against a different database.

If someone change order of sql code blocks in my script - this may cause
lose of it's context (like: use master / use <mydb>..). I wish to have my
sript independed on changes like this and always produce correct result.

What I said above applies to application code. For an installation
script it may be different. Then again, if you want to run it in
several databases, you are going to hate yourself for you put the
database name in.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**fireball** · Jan 2 '07, 04:05 PM

Re: when to use fully qualified names ([database].[schema].object)

Uzytkownik "Erland Sommarskog" <esquel@sommars kog.senapisal w wiadomosci

Use two-part names, not three-part names.

but why??

I wisht to say, for example:

SELECT * FROM somebase.sys.sc hemas
WHERE name = N'someschema'

or (- I don't know which query will be more proper, to obtain schema of
given database):

SELECT * FROM somebase.INFORM ATION_SCHEMA.SC HEMATA
WHERE SCHEMA_NAME = N'someschema'

but without saying:
USE somebase

- (why) is that wrong solution?
hint: I parametrize my database name in my scripts.

**Erland Sommarskog** · Jan 2 '07, 10:25 PM

Re: when to use fully qualified names ([database].[schema].object)

fireball (fireball@onet. kropka.eu) writes:

Uzytkownik "Erland Sommarskog" <esquel@sommars kog.senapisal w wiadomosci

>Use two-part names, not three-part names.

but why??
>
>
I wisht to say, for example:
>
SELECT * FROM somebase.sys.sc hemas
WHERE name = N'someschema'
>
or (- I don't know which query will be more proper, to obtain schema of
given database):
>
SELECT * FROM somebase.INFORM ATION_SCHEMA.SC HEMATA
WHERE SCHEMA_NAME = N'someschema'
>
but without saying:
USE somebase
>
>
- (why) is that wrong solution?
hint: I parametrize my database name in my scripts.

There is not much context in your posts, so the answers you get tend to
be generic.

The problem with specifying the database in application code is that
you get problems if you want to run a second environment on the same
server.

Apparently you are writing some scripts. I would say that the same thing
applies where: the fewer places you specify the database name, the
easier is to change the script to run for a different database.

Now you say that you parameterise the database name in the script. I guess
this is one of the SQLCMD variables, that I will have to admit not paid
too much attention to. If you have a script variable that holds the
database name, I guess it's OK.

But I don't know what your scripts are doing, so it's difficult to say
for sure.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**fireball** · Jan 3 '07, 02:15 PM

Re: when to use fully qualified names ([database].[schema].object)

Uzytkownik "Erland Sommarskog" <esquel@sommars kog.senapisal w wiadomosci

once in my script:
declare @db varchar(255), @sch varchar(255)
set @db = 'somebase'
set @sh = 'someschema'

But I failed trying sql:
create schema somebase.somesc hema
/set @sql = 'create schema ' + quotename(@db) + '.' + quotename(@sch) ;
exec sp_executesql @sql/
- how to name it fully?

**Erland Sommarskog** · Jan 3 '07, 10:35 PM

Re: when to use fully qualified names ([database].[schema].object)

fireball (fireball@onet. kropka.eu) writes:

Uzytkownik "Erland Sommarskog" <esquel@sommars kog.senapisal w wiadomosci
>
once in my script:
declare @db varchar(255), @sch varchar(255)
set @db = 'somebase'
set @sh = 'someschema'
>
But I failed trying sql:
create schema somebase.somesc hema
/set @sql = 'create schema ' + quotename(@db) + '.' + quotename(@sch) ;
exec sp_executesql @sql/
- how to name it fully?

The topic for CREATE SCHEMA starts off

Creates a schema in the current database.

Normally you can get away with a USE first in your dynamic SQL, but
CREATE SCHEMA must be alone in a batch. Well, you could do:

EXSC('USE ' + @db + ' EXEC(''CREATE SCHEMA ' + @sch + ''')')

(But use quotename() to deal with the mess of nested quotes.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

when to use fully qualified names ([database].[schema].object)

when to use fully qualified names ([database].[schema].object)

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment