Post reply

Capture table info when index is created

  • September 25, 2012 at 11:23 am

    #262197

    Hi everyone,

    I am trying to create a DDL trigger to capture the schema and table name of any create/alter/drop index statements that are executed on my server. I am using EventData to capture ObjectName, but that returns only the index name. Does anyone know how I can go about getting this info? Thanks.

  • September 25, 2012 at 11:39 am

    #1542006

    the definition of everything available inside the DDL events are here; the vary based ont he event itself.

    http://schemas.microsoft.com/sqlserver/2006/11/eventdata/

    looking it over, i think you want the field TargetObjectName from the eventdata:

    <xs:complexType name="EVENT_INSTANCE_CREATE_INDEX">

    <xs:sequence>

    <!-- Basic Envelope -->

    <xs:element type="SSWNAMEType" name="EventType"/> <xs:element type="xs:string" name="PostTime"/> <xs:element type="xs:int" name="SPID"/>

    <!-- Server Scoped DDL -->

    <xs:element type="PathType" name="ServerName"/> <xs:element type="SSWNAMEType" name="LoginName"/>

    <!-- DB Scoped DDL -->

    <xs:element type="SSWNAMEType" name="UserName"/>

    <!-- Main Body -->

    <xs:element type="SSWNAMEType" name="DatabaseName"/>

    <xs:element type="SSWNAMEType" name="SchemaName"/>

    <xs:element type="SSWNAMEType" name="ObjectName"/>

    <xs:element type="SSWNAMEType" name="ObjectType"/>

    <xs:element type="SSWNAMEType" name="TargetObjectName"/>

    <xs:element type="SSWNAMEType" name="TargetObjectType"/>

    <xs:element type="EventTag_TSQLCommand" name="TSQLCommand"/>

    </xs:sequence>

    </xs:complexType>

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 25, 2012 at 11:43 am

    #1542011

    WOW, was searching for that info all over and for a long time, THANK YOU! Yes, that is the object I need.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply