473,903 Members | 3,824 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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?

Dec 29 '06 #1
8 19344
please, how to specify database name in schema operation like:
select * from sys.schemas where name = <my-schema...
create/drop schema ...
...
?
Dec 29 '06 #2
Hi

"fireball" <fi******@onet. kropka.euwrote in message
news:en******** **@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
Dec 29 '06 #3
Hi

"fireball" <fi******@onet. kropka.euwrote in message
news:en******** **@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

Dec 29 '06 #4
fireball (fi******@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 29 '06 #5
Uzytkownik "Erland Sommarskog" <es****@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.
Jan 2 '07 #6
fireball (fi******@onet. kropka.eu) writes:
Uzytkownik "Erland Sommarskog" <es****@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 2 '07 #7
Uzytkownik "Erland Sommarskog" <es****@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?

Jan 3 '07 #8
fireball (fi******@onet. kropka.eu) writes:
Uzytkownik "Erland Sommarskog" <es****@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 3 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
405
by: Gravy | last post by:
Hi Is there an easy way to decode type names from a config file. For example I have the following element in my config file: <MyElement type = "myassembly, myclass" /> I want to get the assembly name from this string and then get the class name once the assembly has been loaded. The assembly name may also be a fully qualified name, i.e. has Version and PublicKey info. Thanks for your help
4
4157
by: Jim Garrison | last post by:
I know how to use the name() function to access the name of the current node. How do I get the 'fully qualified' name, consisting of the path from the root to the current node? I.e. <a> <b> <c> </c> </b>
3
3113
by: Jacob Crossley | last post by:
I have two web applications on the same server: http://localhost/ModemUpgrade and http://localhost/TestFormAuth The web.config of ModemUpgrade:
4
4038
by: Aashish Patil | last post by:
Hello, Is it possible to obtain the fully qualified name of a class in c#. What I am looking for is something analogous to Box.class.getName() that exists in Java. Its possible to get this by calling GetType() on an instance but I need to do this in a static method of the same object whose fully qualified name is needed. Thus, there is no instance available on which the GetType()
5
3036
by: fc2 | last post by:
Hi I have a problem with fully qualified names. According to the C# language specification: "Every namespace and type has a fully qualified name, which uniquely identifies the namespace or type amongst all others." However, I have problems compiling the following:
1
2332
by: Erland | last post by:
Hi all, As per my understanding in order to load an assembly using Assembly.Load() you have to provide fully qualified name of the assembly you are trying to load e.g. Assembly asmb=Assembly.Load("System.Windows.Forms,Version=1.0.5000.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"); In this case i will have to provide the whole big string in
3
2570
by: surej | last post by:
hello, please help me how to configure a DNS server in a LAN network using fully qualified domain names.I am using CENTOS distro... redhat configuration will also do.I am having two ISP.should i register to dyndns.org
4
7937
by: Vivek | last post by:
Hi, Generally we work with the 2 part qualified table names in db2 i.e. <schema>. <table name> Is there a more fully qualified table name ? I've seen that a select from <database name>.<schema>.<tabnamealso works. This brings me to the next question. Is it possible to refer to a table in database 2 when connected to database 1 ? If so any privileges that are required for doing that ?
2
2244
by: winkerbean | last post by:
Given the following: class A { class B { A::B( A::B const & ); }; };
0
9999
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11282
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10873
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10983
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9684
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7206
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5894
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6088
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4308
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.