Is it possible to create a STORED PROCEDURE or a FUNCTION or TRIGGER with the same name as TABLE Name?

Answer is NO; let’s analyze answer by taking a temporary table ‘PARENT_TAB’ having the following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Procedure: Now we try to create a PROCEDURE with the same name as table name ie. ‘parent_tab’

create procedure parent_tab

as

begin

      select * from parent_tab

end

Msg 2714, Level 16, State 3, Procedure parent_tab, Line 4

There is already an object named 'parent_tab' in the database.

Urr.. Got error

Function:  Let’s try our luck with function with the same name as table name ie. ‘parent_tab’

create function parent_tab()

returns int

AS

BEGIN

      return (0)

end

Msg 2714, Level 16, State 3, Procedure parent_tab, Line 5

There is already an object named 'parent_tab' in the database.

Again Fail!!

Trigger: Let’s try for trigger

create trigger parent_tab ON parent_tab

instead of delete

AS

    PRINT 'Sorry - you cannot delete this data'

GO

Msg 2714, Level 16, State 2, Procedure parent_tab, Line 4

There is already an object named 'parent_tab' in the database.

Constraint: Will we be allowed to make constraint with the same name as table name, lets see

alter table parent_tab

add constraint parent_tab primary key(first_id)

Msg 2714, Level 16, State 4, Line 1

There is already an object named 'parent_tab' in the database.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

So we fail in all our attempt.

CONCLUSION: We cannot make procedure or function or trigger or constraint with the name as table name.

REASON: Since SQL Server treat all these as objects of database and this object are uniquely defined by the object name and object id that is the reason why we cannot have procedure or function etc with the same name as table name.

Post Reference: Vikram Aristocratic Elfin Share