Check for Existing Index on Column

[lwrcasep]

I am creating a SQL Script that will indexes on some columns for many different clients. They may or may not have indexes to the column that I have specified. Is there a way in the script to check if the column has an index no matter what name?
Example Table SalesPerson has two columns.
PK
Name
I want to index Name but the client might already have Name indexed and I don't want to create another index if exist.
Thanks for any help you can give,
lwrcasep

 

[vongrunt]

Here is one quickie:

IF COLUMNPROPERTY(OBJECT_ID('tablename'), 'columnname', 'IsIndexable') = 1
BEGIN
-- do it
END

I'm not sure how it behaves if column is already covered by composite key.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

vongrunt, that won't work see below

create table blaha (id int)

select COLUMNPROPERTY(OBJECT_ID('blaha'), 'id', 'IsIndexable')
select OBJECTPROPERTY(OBJECT_ID('blaha'), 'IsIndexed')

create index pkWasabi on blaha(id)

select COLUMNPROPERTY(OBJECT_ID('blaha'), 'id', 'IsIndexable')
select OBJECTPROPERTY(OBJECT_ID('blaha'), 'IsIndexed')

'IsIndexable' just checks if you can create an index on that table
here is one way

declare @empty varchar(1)
select @empty = ''

-- 35 is the lenght of the name field of the master.dbo.spt_values table
declare @IgnoreDuplicateKeys varchar(35),
    @Unique varchar(35),
    @IgnoreDuplicateRows varchar(35),
    @Clustered varchar(35),
    @Hypotethical varchar(35),
    @Statistics varchar(35),
    @PrimaryKey varchar(35),
    @UniqueKey varchar(35),
    @AutoCreate varchar(35),
    @StatsNoRecompute varchar(35)

select @IgnoreDuplicateKeys = name from master.dbo.spt_values 
    where type = 'I' and number = 1 --ignore duplicate keys
select @Unique = name from master.dbo.spt_values 
    where type = 'I' and number = 2 --unique
select @IgnoreDuplicateRows = name from master.dbo.spt_values 
    where type = 'I' and number = 4 --ignore duplicate rows
select @Clustered = name from master.dbo.spt_values 
    where type = 'I' and number = 16 --clustered
select @Hypotethical = name from master.dbo.spt_values 
    where type = 'I' and number = 32 --hypotethical
select @Statistics = name from master.dbo.spt_values 
    where type = 'I' and number = 64 --statistics
select @PrimaryKey = name from master.dbo.spt_values 
    where type = 'I' and number = 2048 --primary key
select @UniqueKey = name from master.dbo.spt_values 
    where type = 'I' and number = 4096 --unique key
select @AutoCreate = name from master.dbo.spt_values 
    where type = 'I' and number = 8388608 --auto create
select @StatsNoRecompute = name from master.dbo.spt_values 
    where type = 'I' and number = 16777216 --stats no recompute
select o.name,
  i.name,
  'index description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on
      case when (i.status & 16)<>0 then @Clustered else 'non'+@Clustered end
      + case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end
      + case when (i.status & 2)<>0 then ', '+@Unique else @empty end
      + case when (i.status & 4)<>0 then ', '+@IgnoreDuplicateRows else @empty end
      + case when (i.status & 64)<>0 then ', '+@Statistics else
      case when (i.status & 32)<>0 then ', '+@Hypotethical else @empty end end
      + case when (i.status & 2048)<>0 then ', '+@PrimaryKey else @empty end
      + case when (i.status & 4096)<>0 then ', '+@UniqueKey else @empty end
      + case when (i.status & 8388608)<>0 then ', '+@AutoCreate else @empty end
      + case when (i.status & 16777216)<>0 then ', '+@StatsNoRecompute else @empty end),
  'index column 1' = index_col(o.name,indid, 1),
  'index column 2' = index_col(o.name,indid, 2),
  'index column 3' = index_col(o.name,indid, 3)
from sysindexes i, sysobjects o
where i.id = o.id and
      indid > 0 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255) 
      and o.type = 'U' --user table
      --ignore the indexes for the autostat
      and (i.status & 64) = 0 --index with duplicates
      and (i.status & 8388608) = 0 --auto created index
      and (i.status & 16777216)= 0 --stats no recompute
and (i.status & 2048)<>0
and o.name ='customers' --**change this
and index_col(o.name,indid, 1) ='customerid'  --** change this
      order by o.name

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Ack... friday 'noon ... replace 1 with 0.

Or -- do it with -- don't do it .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

... and property name as well.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

It really is bizzare that you can check for identity, allows null, is computed etc etc etc with COLUMNPROPERTY but not if the column is an index

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

And information_schema views have nothing about indexes?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

only if it's foreign key or primary key
example...mess below

USE Northwind

SELECT tc.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable,cu1.COLUMN_NAME as PrimaryKeyColumName,cu2.COLUMN_NAME as ForeignKeyColumnName
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu1 ON cu1.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu2 ON cu2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

This should work then:

if not exists
(	select SI.name
	from sysindexes SI
	where SI.id = object_id(@tablename) -- table must match
		and SI.status & 96 = 0 -- ignore stats and ITW indexes
		and index_col(@tablename, SI.indid, 1) = @colname -- first key column name must match
		and index_col(@tablename, SI.indid, 2) is null -- and this must be the only key column :)
)
begin
	-- do it
end

... where @tablename and @colname are names of table and column to check.

Note that code will also return "not exists" if table and/or column with specified don't exist... so additional pre-checks won't hurt.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[lwrcasep]

Thanks for the feedback. I have been swamped here at work or I would have responded earlier. We ended up writing a tool into our software to add the indexes so I won't need the script after all. So on to the next project...

Thanks,
Lwrcase.