Can we create Multiple tables with Same Name in a Database ?

PandianS
Posted by PandianS under Sql Server category on | Views : 4560
The answer is YES .

We can create multiple tables with same name under Different Schema(s). How?

To Identify the Default schema:
Select SCHEMA_NAME() 'Default Schema'
Result is: dbo

Whenever we create a new table, The table will be created under "dbo" schema. Because this is the default schema.

Creating a Table:
Use DotnetFunda
Go
Create Table TB_DotnetFunda
(
ID Int Identity,
Col1 Varchar(10)
)
Go
Result is:Command(s) completed successfully.

To Identify the table "TB_DotnetFunda" created under which schema
Use DotnetFunda
Go
select Object_schema_Name(object_id('[TB_DotnetFunda]'))
Result is: dbo

Let's try to Create the Table again:
Use DotnetFunda
Go
Create Table TB_DotnetFunda
(
ID Int Identity,
Col1 Varchar(10)
)
Go
Result is:There is already an object named 'TB_DotnetFunda' in the database.

Because, The same table TB_DotnetFunda trying to create under default schema "dbo", So the table already exists under the schema. Then how to proceed ?

Let's create a new schema named "Funda1"
Use DotnetFunda
Go
CREATE SCHEMA [Funda1]
Result is:Command(s) completed successfully.

Now, We can try to create the same table under "Funda1" schema
Use DotnetFunda
Go
Create Table [Funda1].[TB_DotnetFunda]
(
ID Int Identity,
Col1 Varchar(10)
)
Result is:Command(s) completed successfully.

Let's validate how many tables created with same name under which Schema(s)
Use DotnetFunda
Go
Select [Name],[schema_id] 'Schema ID',SCHEMA_NAME([schema_id]) 'Schema Name' from sys.tables Where [name] ='TB_DotnetFunda'
]Result is:
Name	        Schema ID	Schema Name
TB_DotnetFunda 1 dbo
TB_DotnetFunda 5 Funda1


Now, Two tables have been created with same name under different Schema(s) one table under "dbo" schema and one table under "Funda1" schema in the same Database "DotnetFunda".

Note : DotnetFunda is the Database Name.

Conclusion:
We can create more than one table with same name under different Schema in a Database.

Comments or Responses

Posted by: Raja on: 7/10/2010 Level:Starter | Status: [Member]
Excellent set of collections of code snippets Pandians, I regularly follow you and you are doing great job. Keep it up!

Login to post response