q; create index

I need to check if an index available on table T1 and field F1. If not,
create a non-clustered on F1. How can I do this in a stored procedure?JIM.H. wrote:
> I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?
Stuff the output of sp_helpindex into a temp table, then query that temp
table looking for your field name.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||You could check the sysindexes table:
if not exists (select * from sysindexes where name = 'foo')
BEGIN
PRINT 'the index foo does not exist'
END
If you are running SQL Server 2005 you can use sys.indexes:
select * from sys.indexes
--
Keith Kratochvil
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com...
>I need to check if an index available on table T1 and field F1. If not,
> create a non-clustered on F1. How can I do this in a stored procedure?|||=?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com> wrote in
news:9ED14F33-8BF2-435C-908D-245065910183@.microsoft.com:
> I need to check if an index available on table T1 and field F1. If
not,
> create a non-clustered on F1. How can I do this in a stored
procedure?
Untested, but would that not be something like:
DECLARE
@.index_num int;
SELECT
@.index_num = count(si.*)
FROM
sys.schemas AS ss INNER JOIN
sys.objects AS so ON (ss.schema_id = so.schema_id) INNER JOIN
sys.indexes AS si ON (so.object_id = si.object_id) INNER JOIN
sys.index_columns AS sic ON ((si.object_id = sic.object_id) AND
(si.index_id = sic.index_id)) INNER JOIN
sys.columns as SC ON ((sic.object_id = sc.object_id) AND
(sic.column_id = sc.column_id))
WHERE
SC.name = 'column_name' AND
SO.name = 'table_name' AND
SS.name = 'schema_name';
IF @.index_num = 0
CREATE INDEX ...
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP