Service Broker and system tables

Use master

Alter Database Automart
Set Enable_Broker

Use Automart
--create reply and request message types
Create Message Type
[//AWDB/1DBSample/RequestMessage]
Validation = Well_Formed_xml
GO
Create Message Type
[//AWDB/1DBSample/ReplyMessage]
Validation=well_formed_xml
Go
--create a contract with the message types
Create Contract [//AWDB/1DBSample/SampleContract]
([//AWDB/1DBSample/RequestMessage]
Sent by initiator,
[//AWDB/1DBSample/ReplyMessage]
sent by Target)
Go
--create queue for messages
Create Queue TargetQueue1Db

--create a service for target and initiator

Create service 
[//AWDB/DBSample/TargetService]
On Queue TargetQueue1Db
([//AWDB/1DBSample/SampleContract]) 

Create Queue InitiatorQueue1DB
Create Service
[//AWDB/1DBSample/InitiatorService]
On Queue InitiatorQueue1DB

Declare @initDigHandle UniqueIdentifier
Declare @requestMessage nvarchar(100);

Begin Transaction
Begin Dialog @initDigHandle
From Service
[//AWDB/1DBSample/InitiatorService]
To Service
N'//AWDB/DBSample/TargetService'
on Contract
[//AWDB/1DBSample/SampleContract]
With
Encryption=off

Select @requestMessage=N'Message for Target Service';
Send
on Conversation @initDigHandle
Message type
[//AWDB/1DBSample/RequestMessage]
(@RequestMessage)
Select @requestMessage As SentRequestMsg;
Commit Transaction

Declare @RecvReqDigHandle uniqueidentifier
Declare @RecvReqMsg Nvarchar(100)
Declare @recvReqMsgName sysname

Begin Tran
Waitfor
(
    Receive top(1)
    @RecvReqDigHandle=conversation_handle,
    @RecvReqMsg=message_body,
    @recvReqMsgName=message_type_name
   From TargetQueue1DB 
),
TimeOut 1000
Select @RecvReqMsg As RecievedRequestMsg
if  @recvReqMsgName=N'//AWDB/1DBSample/RequestMessage'
Begin
Declare @replyMsg NVarchar(100)
Select @replyMsg=N'Message for initiator service';
Send on Conversation  @RecvReqDigHandle
Message type
[//AWDB/1DBSample/ReplyMessage]
(@replymsg)
End Conversation  @RecvReqDigHandle
End
Select @replyMsg as SentReplyMsg
Commit transaction
GO

Declare @RecvReqDigHandle uniqueidentifier
Declare @RecvReqMsg Nvarchar(100)
Begin Transaction
Waitfor
(
   Receive top(1)
    @RecvReqDigHandle=conversation_handle,
    @RecvReqMsg=message_body
   From InitiatorQueue1DB 
),
Timeout 1000
End
Conversation @RecvReqDigHandle
Select @RecvReqMsg as RecievedReplyMsg
Commit Transaction

use VenueTracker
exec sp_help 

Use Automart
--there are over 200 of these in sql server
Select name from sys.Tables
Select * from sys.Databases
Select * from sys.Tables
Select * from sys.all_columns where object_id=357576312 and Is_nullable=1
Select * from sys.backup_devices
Select * from sys.database_files
Select * from sys.xml_schema_collections

Select * from sys.conversation