auditing SELECT statements

Is there a way to audit SELECT statements on a table? A
database has a few hundred tables and I was asked to find
the tables which are not used in the application. The same
question for UPDATE, DELETE and INSERT (it is not
desirable to use triggers to trace DML statements).
Thanks in advance, GregYou can use SQL Profiler. You use SQL Profiler to define your trace/audit
first. In the definition, you can filter for SELECT and/or the table name.
Then, you can generate a T-SQL script, using the Script Trace ... option
under the File menu, to run on the server side without any GUI frontend.
Check out the following articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;283790&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;283786&Product=sql2k
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:04f001c39e76$54347780$a401280a@.phx.gbl...
> Is there a way to audit SELECT statements on a table? A
> database has a few hundred tables and I was asked to find
> the tables which are not used in the application. The same
> question for UPDATE, DELETE and INSERT (it is not
> desirable to use triggers to trace DML statements).
> Thanks in advance, Greg|||Hmmm...I've never seen that ability before -- that's very cool!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:e$hF6CpnDHA.1072@.TK2MSFTNGP09.phx.gbl...
> You can use SQL Profiler. You use SQL Profiler to define your trace/audit
> first. In the definition, you can filter for SELECT and/or the table name.
> Then, you can generate a T-SQL script, using the Script Trace ... option
> under the File menu, to run on the server side without any GUI frontend.
> Check out the following articles:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283790&Product=sql2k
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283786&Product=sql2k
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Greg" <anonymous@.discussions.microsoft.com> wrote in message
> news:04f001c39e76$54347780$a401280a@.phx.gbl...
> > Is there a way to audit SELECT statements on a table? A
> > database has a few hundred tables and I was asked to find
> > the tables which are not used in the application. The same
> > question for UPDATE, DELETE and INSERT (it is not
> > desirable to use triggers to trace DML statements).
> >
> > Thanks in advance, Greg
>|||If you have access to the source code of the application you can try
SQLClean by www.lockwoodtech.com . I haven't tried it myself though.
--
Jacco Schalkwijk
SQL Server MVP
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:04f001c39e76$54347780$a401280a@.phx.gbl...
> Is there a way to audit SELECT statements on a table? A
> database has a few hundred tables and I was asked to find
> the tables which are not used in the application. The same
> question for UPDATE, DELETE and INSERT (it is not
> desirable to use triggers to trace DML statements).
> Thanks in advance, Greg|||Linchi, is there a way to get ObjectName in the trace?
When i define a trace definition i can add ObjectName,
ObjectID or OwnerName but they are always empty. Is there
a way to populate them? Thanks for your help. Greg
>--Original Message--
>You can use SQL Profiler. You use SQL Profiler to define
your trace/audit
>first. In the definition, you can filter for SELECT
and/or the table name.
>Then, you can generate a T-SQL script, using the Script
Trace ... option
>under the File menu, to run on the server side without
any GUI frontend.
>Check out the following articles:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;283790&Product=sql2k
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;283786&Product=sql2k
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"Greg" <anonymous@.discussions.microsoft.com> wrote in
message
>news:04f001c39e76$54347780$a401280a@.phx.gbl...
>> Is there a way to audit SELECT statements on a table? A
>> database has a few hundred tables and I was asked to
find
>> the tables which are not used in the application. The
same
>> question for UPDATE, DELETE and INSERT (it is not
>> desirable to use triggers to trace DML statements).
>> Thanks in advance, Greg
>
>.
>