• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

trigger DDL - create sp_

Hi Experts, as I can avoid stored procedures with the prefix "sp_" are created
0
enrique_aeo
Asked:
enrique_aeo
  • 2
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If your question is 'Why shouldn't I create stored procedures with a prefix of 'sp_' it's because SQL Server treats them as system stored procedures (special?) which can be accessed from any database, and having a user-defined stored procedure prefixed sp_ could cause problems if there's a system stored procedure with the same name.

If your question is something else, please ask it in simple English, as it's not obvious reading this question.
0
 
enrique_aeoAuthor Commented:
When a developer trying to create a stored procedmiento that has the sp_ prefix need a DDL trigger is triggered by rollback
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please ask your question in the form of a question.
0
 
enrique_aeoAuthor Commented:
Please support the creation of a trigger that prevents creating a stored procedure where the name begins with prefix "sp_"
0
 
Scott PletcherSenior DBACommented:
CREATE TRIGGER [ddl_trigger_create_proc]
ON DATABASE
AFTER CREATE_PROCEDURE
AS
DECLARE @name nvarchar(128)
SET @name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)')
IF @name LIKE 'sp[_]%'
BEGIN
    RAISERROR ('You cannot create a stored procedure with a name starting with "sp_".  Re-create with a different name!', 16, 1)
    ROLLBACK TRANSACTION
END --IF
GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now