Monitoring changes in any table in an instance

Is there a way to monitor changes in any tables of an instance? I have a
database driven application that I want to reverse engineer. I want to find
out if a function would perform changes to which tables in the instance. Is
there a realistic way to do this? Thank you.
Lito Kusnadi
Lito
CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventdata AS XML
SET @.eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
@.eventdata)
GO
The trigger simply extracts all event attributes
of interest from the eventdata() function using XQuery,
and inserts those into the AuditDDLEvents table. To test the trigger,
submit a few DDL statements and query the audit table:
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
ALTER TABLE T1 ADD col2 INT NULL
ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
SELECT * FROM AuditDDLEvents
SELECT posttime, eventtype, loginname,
CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime
"Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> Is there a way to monitor changes in any tables of an instance? I have a
> database driven application that I want to reverse engineer. I want to
> find
> out if a function would perform changes to which tables in the instance.
> Is
> there a realistic way to do this? Thank you.
> --
> Lito Kusnadi
>
|||In addition to Uri's recommendation you may want to take a look at the
information available in SQL Server 2005 default trace.
Take a look at this report in Management Studio, Reports - Standard Reports
- Schema Changes History.
Also see the path of the trace file by running
select * from sys.traces
where the default trace is usually id 1.
Hope this helps,
Ben Nevarez
"Uri Dimant" wrote:

> Lito
> CREATE TABLE AuditDDLEvents
> (
> LSN INT NOT NULL IDENTITY,
> posttime DATETIME NOT NULL,
> eventtype SYSNAME NOT NULL,
> loginname SYSNAME NOT NULL,
> schemaname SYSNAME NOT NULL,
> objectname SYSNAME NOT NULL,
> targetobjectname SYSNAME NOT NULL,
> eventdata XML NOT NULL,
> CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
> )
> GO
> CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
> DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.eventdata AS XML
> SET @.eventdata = eventdata()
> INSERT INTO dbo.AuditDDLEvents(
> posttime, eventtype, loginname, schemaname,
> objectname, targetobjectname, eventdata)
> VALUES(
> CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
> CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
> CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
> @.eventdata)
> GO
> The trigger simply extracts all event attributes
> of interest from the eventdata() function using XQuery,
> and inserts those into the AuditDDLEvents table. To test the trigger,
> submit a few DDL statements and query the audit table:
>
> CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
> ALTER TABLE T1 ADD col2 INT NULL
> ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
> CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
> SELECT * FROM AuditDDLEvents
> SELECT posttime, eventtype, loginname,
> CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
> AS tsqlcommand
> FROM dbo.AuditDDLEvents
> WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
> ORDER BY posttime
>
> "Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
> news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
>
>