Thursday, October 13, 2011

create Multiple tables with Same Name in a Database ?

The answer is YES .
We can create multiple tables with same name under Different Schema(s).
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 SQLServerBuddy
Go
Create Table TB_Table1(ID Int Identity,Col1 Varchar(10))
Go
Result is: Command(s) completed successfully.

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

Let's try to Create the Table again:
Use SQLServerBuddy
Go
Create Table TB_Table1(ID Int Identity,Col1 Varchar(10))
Go
Result is:There is already an object named 'TB_Table1' in the database.
Because, The same table TB_Table1 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 "SQLBuddy"
Use SQLServerBuddy
Go
CREATE SCHEMA [SQLBuddy]
Result is:Command(s) completed successfully.

Now, We can try to create the same table under "SQLBuddy" schema
Use SQLServerBuddy
Go
Create Table [SQLBuddy].[TB_Table1](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 SQLServerBuddy
Go
Select [Name],[schema_id] 'Schema ID',SCHEMA_NAME([schema_id]) 'Schema Name' from sys.tables Where [name] ='TB_Table1'

Result is:

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

Note : SQLServerBuddy is the Database Name.

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

No comments:

Post a Comment