Find out heavily accessed tables in the database

Dear All,
I want to make access counter statistic monthly for each table in the
database. The purpose is to find out the heavily accessed tables.
Does anyone know how to do that?
Thanks
Robert LieConcider creating the table that has information about all the tables in the
database.
Write a trigger for each table in the database. The trigger should update
the hit-count column in the table that you created recently.
This is a tedious process but i believe that it answers your question.
thanks and regards
Chandra
"Robert Lie" wrote:

> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
>|||Could you give some code example about the trigger you're proposing to.
Thanks
Robert Lie
Chandra wrote:[vbcol=seagreen]
> Concider creating the table that has information about all the tables in t
he
> database.
> Write a trigger for each table in the database. The trigger should update
> the hit-count column in the table that you created recently.
> This is a tedious process but i believe that it answers your question.
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>|||Here is the sample:
===============
Sample Table should look like:
CREATE TABLE STATS(
TABLE_NAME varchar(20),
HIT_COUNT integer default 0
)
Say you are writing trigger on Employee Table
insert into STATS values ('Employee')
Now:
CREATE TRIGGER TR_EMP_STAT
ON EMPLOYEE
FOR INSERT,UPDATE, DELETE
AS
BEGIN
UPDATE STATS
SET HIT_COUNT = HIT_COUNT + 1
WHERE TABLE_NAME = 'EMPLOYEE'
END
GO
================
hope you are satisified...
thanks and regards
Chandra
"Robert Lie" wrote:

> Could you give some code example about the trigger you're proposing to.
> Thanks
> Robert Lie
>
> Chandra wrote:
>|||Ok. Thanks for your suggestion.
But I need more than that, I need to know how many users who access the
tables which means it can be SELECT, INSERT, UPDATE dan DELETE.
Thanks
Chandra wrote:[vbcol=seagreen]
> Here is the sample:
> ===============
> Sample Table should look like:
> CREATE TABLE STATS(
> TABLE_NAME varchar(20),
> HIT_COUNT integer default 0
> )
>
> Say you are writing trigger on Employee Table
> insert into STATS values ('Employee')
> Now:
> CREATE TRIGGER TR_EMP_STAT
> ON EMPLOYEE
> FOR INSERT,UPDATE, DELETE
> AS
> BEGIN
> UPDATE STATS
> SET HIT_COUNT = HIT_COUNT + 1
> WHERE TABLE_NAME = 'EMPLOYEE'
> END
> GO
> ================
> hope you are satisified...
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>|||Robert
Have you looked at SQL Server Profiler to capture data ?
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie|||I think is quite difficult to use SQL Profiler for that purpose since we
have to run the SQL Profiler in certain period.
Uri Dimant wrote:
> Robert
> Have you looked at SQL Server Profiler to capture data ?
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
>
>
>|||Robert
You don't need to run Profiler on the server, run it on the client connected
to the server.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I think is quite difficult to use SQL Profiler for that purpose since we
> have to run the SQL Profiler in certain period.
>
> Uri Dimant wrote:|||I agree with Uri , Profile is the best option for Roberts requirement.
Regards
Vishal Gandhi
"Uri Dimant" wrote:

> Robert
> You don't need to run Profiler on the server, run it on the client connect
ed
> to the server.
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>

Find out heavily accessed tables in the database

Dear All,
I want to make access counter statistic monthly for each table in the
database. The purpose is to find out the heavily accessed tables.
Does anyone know how to do that?
Thanks
Robert Lie
Concider creating the table that has information about all the tables in the
database.
Write a trigger for each table in the database. The trigger should update
the hit-count column in the table that you created recently.
This is a tedious process but i believe that it answers your question.
thanks and regards
Chandra
"Robert Lie" wrote:

> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
>
|||Could you give some code example about the trigger you're proposing to.
Thanks
Robert Lie
Chandra wrote:[vbcol=seagreen]
> Concider creating the table that has information about all the tables in the
> database.
> Write a trigger for each table in the database. The trigger should update
> the hit-count column in the table that you created recently.
> This is a tedious process but i believe that it answers your question.
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>
|||Here is the sample:
===============
Sample Table should look like:
CREATE TABLE STATS(
TABLE_NAME varchar(20),
HIT_COUNT integer default 0
)
Say you are writing trigger on Employee Table
insert into STATS values ('Employee')
Now:
CREATE TRIGGER TR_EMP_STAT
ON EMPLOYEE
FOR INSERT,UPDATE, DELETE
AS
BEGIN
UPDATE STATS
SET HIT_COUNT = HIT_COUNT + 1
WHERE TABLE_NAME = 'EMPLOYEE'
END
GO
================
hope you are satisified...
thanks and regards
Chandra
"Robert Lie" wrote:

> Could you give some code example about the trigger you're proposing to.
> Thanks
> Robert Lie
>
> Chandra wrote:
>
|||Ok. Thanks for your suggestion.
But I need more than that, I need to know how many users who access the
tables which means it can be SELECT, INSERT, UPDATE dan DELETE.
Thanks
Chandra wrote:[vbcol=seagreen]
> Here is the sample:
> ===============
> Sample Table should look like:
> CREATE TABLE STATS(
> TABLE_NAME varchar(20),
> HIT_COUNT integer default 0
> )
>
> Say you are writing trigger on Employee Table
> insert into STATS values ('Employee')
> Now:
> CREATE TRIGGER TR_EMP_STAT
> ON EMPLOYEE
> FOR INSERT,UPDATE, DELETE
> AS
> BEGIN
> UPDATE STATS
> SET HIT_COUNT = HIT_COUNT + 1
> WHERE TABLE_NAME = 'EMPLOYEE'
> END
> GO
> ================
> hope you are satisified...
> thanks and regards
> Chandra
>
> "Robert Lie" wrote:
>
|||Robert
Have you looked at SQL Server Profiler to capture data ?
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> I want to make access counter statistic monthly for each table in the
> database. The purpose is to find out the heavily accessed tables.
> Does anyone know how to do that?
> Thanks
> Robert Lie
|||I think is quite difficult to use SQL Profiler for that purpose since we
have to run the SQL Profiler in certain period.
Uri Dimant wrote:
> Robert
> Have you looked at SQL Server Profiler to capture data ?
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:el9jbP6SFHA.2756@.tk2msftngp13.phx.gbl...
>
>
|||Robert
You don't need to run Profiler on the server, run it on the client connected
to the server.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I think is quite difficult to use SQL Profiler for that purpose since we
> have to run the SQL Profiler in certain period.
>
> Uri Dimant wrote:
|||I agree with Uri , Profile is the best option for Roberts requirement.
Regards
Vishal Gandhi
"Uri Dimant" wrote:

> Robert
> You don't need to run Profiler on the server, run it on the client connected
> to the server.
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:ONyqpy7SFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
>

Find a word in entire database

SQL server 2000
Hi
I want to search a field value in entire database.
Example: Search for word "Ontario" in all rows of all tables in the
database. I do not want to search column_names but basically all rows in all
columns of the database for a particular word.
Thanks
ontario, canada
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||If you need to do rather convoluted search (e.g. with regular expressions),
you could also consider dump everything out to files and search files for
your string. Probably more troublesome than it's worth, but could be useful
for certain scenarios.
Linchi
"db" wrote:

> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in all
> columns of the database for a particular word.
> Thanks
> ontario, canada
|||IIRC ApexSQL Edit has this functionality built in. Pretty slick.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"db" <db@.discussions.microsoft.com> wrote in message
news:5B906D6F-9018-41C2-BB73-22A4AACDF41F@.microsoft.com...
> SQL server 2000
> Hi
> I want to search a field value in entire database.
> Example: Search for word "Ontario" in all rows of all tables in the
> database. I do not want to search column_names but basically all rows in
> all
> columns of the database for a particular word.
> Thanks
> ontario, canada