Is it impossible to have schema1.dave and schema2.dave as tables in the same DB?

[David Thielen]

Is it impossible to have 2 tables with the same name but different
schemas in Sql Server? Not unusual or frowned on - but flat out
impossible? I tried to do it and failed, but I am not an expert at Sql
Server.

thanks - dave


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[David Thielen]

Ok, all of our key stuff is doable then. Thank you very much - I don't
trust based on what I can/can't do because my Sql Server skills are
mid-level at best.

thanks - dave

Apologies - read too fast.

Yes, it is *IMPOSSIBLE* to have two tables with the same name in SQL Server.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Gregory A. Beamer]

Is it impossible to have 2 tables with the same name but different
schemas in Sql Server? Not unusual or frowned on - but flat out
impossible? I tried to do it and failed, but I am not an expert at Sql
Server.

If you are using SQL Server 2008, the following script will run:

CREATE SCHEMA Schema1
GO

CREATE SCHEMA Schema2
GO

CREATE TABLE Schema1.TestTable
(
TestTableId int PRIMARY KEY IDENTITY(1,1)
, SomeText varchar(50) NOT NULL
)
GO

CREATE TABLE Schema2.TestTable
(
TestTableId int PRIMARY KEY IDENTITY(1,1)
, SomeText varchar(50) NOT NULL
)
GO

This does not work in prior versions, AFAIK, since users, not schemas,
own tables.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Gregory A. Beamer]

@TK2MSFTNGP04.phx.gbl:

You are quite correct, and I apologise to David. Stupidly, I had not
considered SQL Server 2008...

As you correctly pointed out, however, it is not wise to have the same
table names in different schemas. It is an accident waiting to happen. And
most reasons you might think to do it can be solved by properly
architecting the database.



--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[David Thielen]

I couldn't agree more! This should at the very least be subject to a
configuration option which is off by default.

Sometimes Microsoft allows things which, to me at least, are just
unfathomable...

But when writing a library for others to use - we must work when they
do things we don't approve of...

thanks - dave


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Lingzhi Sun [MSFT]]

Hi David,

I have posted a reply yesterday, but I did not realize that my post was not
sent out successfully. Sorry for that. In my post, I pointed out the
identifiers of SQL Server Objects.

Quote from the MSDN documentation, Using Identifiers As Object Names
(http://msdn.microsoft.com/en-us/library/ms187879.aspx)

"The complete name of an object is made up of four identifiers: the server
name, database name, schema name, and object name. They appear in the
following format:
server_name .[database_name].[schema_name].object_name
| database_name.[schema_name].object_name
| schema_name.object_name
| object_name
...
An object name that specifies all four parts is known as a fully qualified
name. Each object that is created in Microsoft SQL Server must have a
unique, fully qualified name. For example, there can be two tables named
xyz in the same database if they have different owners."

Based on the MSDN documentation, the scenario (same table name with
different schema names) is supported in both SQL Server 2008 and SQL Server
2005. Also, from my testing result, Gregory’s script can work fine in both
SQL Server 2008 and SQL Server 2005.

Quote from Gregory's post:
"This does not work in prior versions, AFAIK, since users, not schemas, own
tables."

I think it is for SQL Server 2000. From SQL Server 2005, by default same
table name under different schemas is supported.


If you have any questions regarding this case, please feel free to let me
know.

Have a nice day!

Best Regards,
Lingzhi Sun
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.