finding a word, submitting part of it

Dear All

Does anyone know any syntax that will allow me to find entries in a atable when only part of a word is entered. I have a table that has been full txt indexed at the moment I am using the CONTAINS keyword to search fields. This works fine 'select * from tblX where contains(colX, 'gill')', gives me back what i want, but what if i type this 'select * from tblX where contains(colX, 'ill')' it doesn't find anything. What i want is a command that will search all the words in a table column. Does anyone know of a command that will do this?

ThanksI got into the CONTAINS (T-SQL) syntax, and I found only prefix_terms like 'ill*', but I didn't found a solution for a '*ill' condition. Using the LIKE operator isn't an option either. I hope that other guys can help you. :(|||Well, LIKE is an option, just not a very efficient one. But, it's there for just this type of search.

SELECT * FROM tblX WHERE colX LIKE '%ill'

You can also replace the wildcard % with:

_ looks for any single char

[] looks for any chars or range of chars you specify ([a-q], [abcd], [1-9], etc)

[^] same as above, but exclusion chars ([^a-f] means anything but a through f)|||Another option is to use CHARINDEX:

where charindex('ill', colx) > 0

I don't know how this compares to LIKE for speed, but I suspect it would be faster because it has less functionality (no wildcards) and thus may have less processing overhead.

blindman|||I think you are right. Though, any time you ask SQL to do string processing, you are going to take a hit. They seem to have build in several levels of functionality here, getting progressively deeper as you need them. Can't think of why they would do that vs a sigle flexible function, unless the performance would dive.

FIND THE FOREIGN KEY FOR A TABLE

Dear All,
I want to know, how to retrieve the foreign key for a table using T-SQL.
Regards,
SathiamoorthyHi
Look at Aaron's example
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='Customers'
ORDER BY
1,2,3,4
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:uIzkS2wKGHA.648@.TK2MSFTNGP14.phx.gbl...
> Dear All,
> I want to know, how to retrieve the foreign key for a table using T-SQL.
> Regards,
> Sathiamoorthy
>

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 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:
> 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|||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:
> > 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
> >>
>|||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:
> 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:
>>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
>>|||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...
>>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
>
>|||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...
> 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...
> >
> >>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 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...
> > 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...
> > >
> > >>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
> > >
> > >
> > >
>
>