Extracting SetOptions in the TSQLCommand section of EVENTDATA

How do I parse out the SetOptions in the EventData that is generated
for DDL events? I would like to extract the various options and the
values. Seems I would need to do this dynamically since I don't which
set options would be present each time. Since these are not elements
like the other entries, I'm having trouble getting at them.
<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
What about:
declare @.x xml
set @.x = N'<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>';
select n.value('local-name(.)', 'nvarchar(100)') as opt_name
, n.value('.', 'nvarchar(100)') as opt_value
from @.x.nodes('/EVENT_INSTANCE/TSQLCommand/SetOptions/@.*') as T(n)
Best regards
Michael
"SteveM" <stephencmyers@.hotmail.com> wrote in message
news:1140617241.326529.71400@.o13g2000cwo.googlegro ups.com...
> How do I parse out the SetOptions in the EventData that is generated
> for DDL events? I would like to extract the various options and the
> values. Seems I would need to do this dynamically since I don't which
> set options would be present each time. Since these are not elements
> like the other entries, I'm having trouble getting at them.
>
> <EVENT_INSTANCE>
> <EventType>ALTER_PROCEDURE</EventType>
> <PostTime>2006-02-22T07:42:57.830</PostTime>
> <SPID>55</SPID>
> <ServerName>DBServer1\DEVELOPMENT</ServerName>
> <LoginName>AAA/BBB</LoginName>
> <UserName>dbo</UserName>
> <DatabaseName>EventData</DatabaseName>
> <SchemaName>dbo</SchemaName>
> <ObjectName>Process_Audit_Messages_pr</ObjectName>
> <ObjectType>PROCEDURE</ObjectType>
> <TSQLCommand>
> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
> ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
> <CommandText>/*
> Create procedure xxx as ...
> </CommandText>
> </TSQLCommand>
> </EVENT_INSTANCE>
>
|||Thanks! Exactly what I was looking for.

Extracting SetOptions in the TSQLCommand section of EVENTDATA

How do I parse out the SetOptions in the EventData that is generated
for DDL events? I would like to extract the various options and the
values. Seems I would need to do this dynamically since I don't which
set options would be present each time. Since these are not elements
like the other entries, I'm having trouble getting at them.
<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>What about :
declare @.x xml
set @.x = N'<EVENT_INSTANCE>
<EventType>ALTER_PROCEDURE</EventType>
<PostTime>2006-02-22T07:42:57.830</PostTime>
<SPID>55</SPID>
<ServerName>DBServer1\DEVELOPMENT</ServerName>
<LoginName>AAA/BBB</LoginName>
<UserName>dbo</UserName>
<DatabaseName>EventData</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Process_Audit_Messages_pr</ObjectName>
<ObjectType>PROCEDURE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>/*
Create procedure xxx as ...
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>';
select n.value('local-name(.)', 'nvarchar(100)') as opt_name
, n.value('.', 'nvarchar(100)') as opt_value
from @.x.nodes('/EVENT_INSTANCE/TSQLCommand/SetOptions/@.*') as T(n)
Best regards
Michael
"SteveM" <stephencmyers@.hotmail.com> wrote in message
news:1140617241.326529.71400@.o13g2000cwo.googlegroups.com...
> How do I parse out the SetOptions in the EventData that is generated
> for DDL events? I would like to extract the various options and the
> values. Seems I would need to do this dynamically since I don't which
> set options would be present each time. Since these are not elements
> like the other entries, I'm having trouble getting at them.
>
> <EVENT_INSTANCE>
> <EventType>ALTER_PROCEDURE</EventType>
> <PostTime>2006-02-22T07:42:57.830</PostTime>
> <SPID>55</SPID>
> <ServerName>DBServer1\DEVELOPMENT</ServerName>
> <LoginName>AAA/BBB</LoginName>
> <UserName>dbo</UserName>
> <DatabaseName>EventData</DatabaseName>
> <SchemaName>dbo</SchemaName>
> <ObjectName>Process_Audit_Messages_pr</ObjectName>
> <ObjectType>PROCEDURE</ObjectType>
> <TSQLCommand>
> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
> ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
> <CommandText>/*
> Create procedure xxx as ...
> </CommandText>
> </TSQLCommand>
> </EVENT_INSTANCE>
>|||Thanks! Exactly what I was looking for.

extracting DDLs in SQL Server

I really apprciate any idea's

On extracting ddl's for each object on a separate file in SQL Server?

Something like ddl's for:
procedure1.sql, procedure2.sql....so on procedure100.sql
onto a file server.

Does SQL Server has any API's like Oracle's dbms_metadata?

Or can we use SQL enterprise manager to do this job?

Thanks, Madhavi.In Query analyser, you can right click on most objects in the object browser
and select "Script object to" and choose your reference....

In enterprise manager you can right click on a DB and select "Generate SQL Script"...from there a window appears where you can choose what object types to script (Tables/UDF/Stored procs/User defined types...)|||Thanks

How can I automate this process? As, I will have to schedule this process to run daily.

Madhavi.|||I'd use SQL-DMO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_85f7.asp).

-PatP