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

  • Thread starter Thread starter David Thielen
  • Start date Start date
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.
Click to expand...

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! |
*******************************************
 
@TK2MSFTNGP04.phx.gbl:

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


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! |
*******************************************
 
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.
 
Back
Top