DDL Triggers don't catch calls to sp_rename

Discussion in 'microsoft.public.sqlserver.programming' started by AW, Aug 9, 2007.

  1. AW

    AW Guest


    Ich was about to use DDL Triggers to extract all DDL-Level Events to a table
    for later Scripting.
    But when testing I found out that if a developer just right-clicks a Table
    and renames it inside Management Studio, that event doesn't get caught.
    Same when renaming a Column inside a table.
    Reason seems that Management Studio calls sp_rename behind. And that does
    not get caught.
    I can not take the risk that Developers don't use standars SQL like "ALTER

    Any best Practices already?
    Can't imagine nobody stumbled over this.. but did not find anything on that
    anywhere yet.

    Thanks for reading,

    AW, Aug 9, 2007
    1. Advertisements

  2. AW,

    This is documented in the Book Online, at least the May 2007 version that I
    am currently using. Under the topics "Event Groups For Use With DDL
    Triggers" and "DDL Events for Use with DDL Triggers" it has this note:

    Certain system stored procedures that perform DDL-like operations can also
    fire DDL triggers. Test your DDL triggers to determine their responses to
    system stored procedures that are run. For example, the CREATE TYPE
    statement and sp_addtype stored procedure will both fire a DDL trigger that
    is created on a CREATE_TYPE event. However, the sp_rename stored procedure
    does not fire any DDL triggers.

    Yes, I also consider this a problem that should be fixed, and so do many

    Russell Fields, Aug 10, 2007
    1. Advertisements

  3. AW

    AW Guest

    Thank you Russel for that response.
    Also for the link.
    As I do not think altering those sp_'s is a good idea I guess I have to wait
    and hope for Katmai. :-/

    AW, Aug 11, 2007
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.