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

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.


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

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

Hi

[quoted text, click to view]
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.

[quoted text, click to view]
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.

[quoted text, click to view]

John

Hi

[quoted text, click to view]

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

fireball (fireball@onet.kropka.eu) writes:
[quoted text, click to view]

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-parameterisation). 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.


[quoted text, click to view]

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@sommarskog.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

Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci

[quoted text, click to view]
but why??


I wisht to say, for example:

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

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

SELECT * FROM somebase.INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = N'someschema'

but without saying:
USE somebase


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

fireball (fireball@onet.kropka.eu) writes:
[quoted text, click to view]

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@sommarskog.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

Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal 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.someschema
/set @sql = 'create schema ' + quotename(@db) + '.' + quotename(@sch);
exec sp_executesql @sql/
- how to name it fully?

fireball (fireball@onet.kropka.eu) writes:
[quoted text, click to view]

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@sommarskog.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