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?

SELECT

C.[Name] AS [IndexedColumns]

FROM sys.indexes I

INNER JOIN sys.index_columns IC ON (I.index_id = IC.index_ID)

INNER JOIN sys.columns C ON (IC.column_ID = C.column_ID)

WHERE

OBJECT_NAME(I.OBJECT_ID) = 'StoreContact'

|||

Actually it is a bit more than that. There are a few things you have to take care of, including included columns. This query will do it:

use tempdb
go
drop table t1
go
create table T1
(
T1Id int primary key,
F1 int,
F2 int,
F3 int,
F4 int
)
create index T1_F2 on t1(f2)
create index T1_F3andF4 on t1(f3,f4)
create index T1_F1includeF4 on t1(f1) include (f4)
go

--the first subquery gets the primary columns in the index. So set the columns
--you want to match in the IN clause. Then set the number to match the number
--of matches you desire.

--the second does the included columns, something you have to consider for 2005
--because it could look like you have an index that you don't actually have if
-- create index T1_F1includeF4 on t1(f1) include (f4)
--is created instead of
--create index T1_F1includeF4 on t1(f1,f4)

select i.name, type_desc,is_unique
from sys.indexes as i
where object_name(i.object_id) = 'T1'
and (select count(*)
from sys.columns as sc
join sys.index_columns as ic
on sc.object_id = ic.object_id
and sc.column_id = ic.column_id
and ic.is_included_column = 0
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and sc.name in ('f1')) = 2 --match # of cols in in clause
and (select count(*)
from sys.columns as sc
join sys.index_columns as ic
on sc.object_id = ic.object_id
and sc.column_id = ic.column_id
and ic.is_included_column = 1
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and sc.name in ('')) = 0 --match # of cols in in clause
go

|||

Jim, if your application is large enough you will soon run into maintainance nightmare if you are not sure whether you have the specific index on specific table. Checking whether the index exists or not and creating it is one-time solution.

Create some table that tracks your database version. If you need to do any schema change, change the db version correspondingly.

For a fixed db version, your table schemas should be unambigous. You might have an sql file for each table, which should include the create table command AND create index commands. Say, if you have added an index(iDATE) for table T on version 6.35, then all the clients that have db version 6.35 should have iDATE on T, and NONE should have such index if their db version is < 6.35.
If you are interested, post your application specifics, and we could discuss specific version changing schemas.
Good luck.

|||

Jim, if your application is large enough you will soon run into maintainance nightmare if you are not sure whether you have the specific index on specific table. Checking whether the index exists or not and creating it is one-time solution.

No doubt. I use this kind of code from my data modeling tool to create indexes if they are on the model but not in the actual database.