index issues

I would like to know what are the index exists on which table in my entire database.And also i want to rebuild some of them.
How can i do these two things.
Thanks.You can use sp_helpindex tablename to find the indexes on a particular table. This will also give you the columns and the order of them in the index. As for rebuilding them, you have three options. Drop and rebuild, dbcc dbreindex, and dbcc indexdefrag. All three have their pluses, minus, and idiosyncracies. Generally I use them in the following way:

dbcc dbreindex: Small tables. This will create a copy of all of the indexes (remember clustered indexes are really the table itself), and do a quick switch to the new copies.

dbcc indexdefrag: Large indexes where the index is alone in a filegroup. Also, if the database is in simple recovery mode. I am not terribly impressed with the efficiency of indexdefrag, but it does not lock up the table. This is highly valuable for systems that can not be brought down for maintenance. On the downside, it will run your transaction log pretty well, if you are not in simple recovery mode.

drop/rebuild. A decent all purpose tool, but you need to have a bit of downtime available to you, as the tables will have select locks on them, while you rebuild the indices. You also need to have the scripts for the indices handy (which is why I encourage the use of sp_helpindex), in case anything goes wrong. Remember also, the clustered index should be the last to be dropped, and the first to be rebuilt. Also, note. This does not work on Primary Key indices that have a foreign key referencing them.

Hope this helps.|||Thank u very much.

Index / Search question

Is a full-text search the only way to find specific data in a table or
database? Can a full-text, indexed search scan an entire database instead of
individual tables?
Thanks!
Dave W
No, AFAIK thats no working, but you can use the script from Vysh.(even this
is not using any fulltext features, but extending your search all columns in
al tables):
http://vyaskn.tripod.com/search_all_...all_tables.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> Is a full-text search the only way to find specific data in a table or
> database? Can a full-text, indexed search scan an entire database instead
> of
> individual tables?
> Thanks!
> --
> Dave W
|||Thanks Jens! Your reply was exactly what I needed!
Dave W
"Jens Sü?meyer" wrote:

> No, AFAIK thats no working, but you can use the script from Vysh.(even this
> is not using any fulltext features, but extending your search all columns in
> al tables):
> http://vyaskn.tripod.com/search_all_...all_tables.htm
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
>
>

Index / Search question

Is a full-text search the only way to find specific data in a table or
database? Can a full-text, indexed search scan an entire database instead o
f
individual tables?
Thanks!
--
Dave WNo, AFAIK thats no working, but you can use the script from Vysh.(even this
is not using any fulltext features, but extending your search all columns in
al tables):
http://vyaskn.tripod.com/search_all..._all_tables.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> Is a full-text search the only way to find specific data in a table or
> database? Can a full-text, indexed search scan an entire database instead
> of
> individual tables?
> Thanks!
> --
> Dave W|||Thanks Jens! Your reply was exactly what I needed!
--
Dave W
"Jens Sü?meyer" wrote:

> No, AFAIK thats no working, but you can use the script from Vysh.(even thi
s
> is not using any fulltext features, but extending your search all columns
in
> al tables):
> http://vyaskn.tripod.com/search_all..._all_tables.htm
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
>
>

Index / Search question

Is a full-text search the only way to find specific data in a table or
database? Can a full-text, indexed search scan an entire database instead of
individual tables?
Thanks!
--
Dave WNo, AFAIK thats no working, but you can use the script from Vysh.(even this
is not using any fulltext features, but extending your search all columns in
al tables):
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> Is a full-text search the only way to find specific data in a table or
> database? Can a full-text, indexed search scan an entire database instead
> of
> individual tables?
> Thanks!
> --
> Dave W|||Thanks Jens! Your reply was exactly what I needed!
--
Dave W
"Jens Sü�meyer" wrote:
> No, AFAIK thats no working, but you can use the script from Vysh.(even this
> is not using any fulltext features, but extending your search all columns in
> al tables):
> http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Dave W" <DaveW@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2E5175AB-9EDD-4D34-92A2-51E683161FF9@.microsoft.com...
> > Is a full-text search the only way to find specific data in a table or
> > database? Can a full-text, indexed search scan an entire database instead
> > of
> > individual tables?
> >
> > Thanks!
> > --
> > Dave W
>
>