are columns indexed ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

are columns indexed ?

Author

Topic

bodman
Starting Member

4 Posts

Posted - 2006-12-21 : 10:44:32

Hi ?
I need to know in run time weather certain columns are indexed, meaning these columns only are segments of an index. how do I do that ?

please help,

thanks, Omri Levin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 10:47:27

INDEX_COL() may be of help to you.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

bodman
Starting Member

4 Posts

Posted - 2006-12-23 : 10:24:51

thnx Harsh,
got it finally,

Omri Levin (India)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-23 : 12:32:48

Can you share with us what you have found?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

bodman
Starting Member

4 Posts

Posted - 2006-12-23 : 23:20:25

of course I'll happily share, I hope it doesn't have major problems... anyway, here it is, the function dm_string2table used in the procedure parses a delimited string and returns a table.

/*
the procedure checks if the table @InTableName and the column list @Segments (comma seperated)
exist and if the specified columns have an index on them
@RetCode = -1 if the table name or one of columns specified in @Segments, don't exist
@RetCode = 1 if the columns specified in @Segments have an index on them
@RetCode = 0 if the columns specified in @Segments DON'T have an index on them
*/
ALTER procedure dm_sp_IsIndxed
@InTableName as nvarchar(128),
@Segments as nvarchar(1024),
@RetCode as int OUTPUT

AS
begin

declare @iCount as integer
declare @NumberOfSegmentsInput as integer
declare @lcl_HelpIndex table (Index_name varchar(255), Dexcription varchar(500), IndexKeys varchar(500))
declare @lcl_TableInput table (item_number int, item varchar(100))
declare @lcl_TableHI table (item_number int, item varchar(100))
declare @CurrentIndexKeys as varchar(500)
declare @Current_Index_name varchar(255)
declare @Previous_Index_name varchar(255)

/* default @RetCode = 0 (no index on the given columns) */
set @RetCode = 0

/* parse segments parameter into a table */
insert into @lcl_TableInput
select * from master.dbo.dm_string2table(@Segments, ',')

/* determine and store the number of parameters */
select @NumberOfSegmentsInput = count(*) from @lcl_TableInput

/* check if columns really exist in the table */
select @iCount = count(*) from syscolumns a, sysobjects b, @lcl_TableInput c
where a.id = b.id
and b.name = @InTableName
and lower(a.name) collate Hebrew_Bin = lower(ltrim(rtrim(c.item)))

/* if the parameters don't match (table or columns don't exist) return -1 */
if @iCount < @NumberOfSegmentsInput
begin
set @RetCode = -1
goto CleanUp
end

/* either create or emty the temporary table that holds the sp_helpindex output */
if not exists(select 1 from dbo.sysobjects where name = '#dm_lcl_HelpIndex' and xType='U')
create table #dm_lcl_HelpIndex (Index_name varchar(255), Dexcription varchar(500), IndexKeys varchar(500))
else
truncate table #dm_lcl_HelpIndex

/* populate temp table with sp_helpindex output */
insert into #dm_lcl_HelpIndex
exec sp_helpindex @InTableName

select @iCount = 0
select @iCount = count(*) from #dm_lcl_HelpIndex

/* no indexes defined on the input table */
if @iCount = 0
goto CleanUp

set @Current_Index_name = ''
set @Previous_Index_name = ''

/* read first index record */
select top 1 @Current_Index_name = Index_name, @CurrentIndexKeys = IndexKeys
from #dm_lcl_HelpIndex
order by Index_name

while @Current_Index_name <> @Previous_Index_name
begin
set @Previous_Index_name = @Current_Index_name

insert into @lcl_TableHI
select * from master.dbo.dm_string2table (@CurrentIndexKeys, ',')

select @iCount = count(*)
from @lcl_TableHI a, @lcl_TableInput b
where LOWER(a.item) = LOWER(b.item)

if @iCount = @NumberOfSegmentsInput
begin
set @RetCode = 1
goto CleanUp
end

/* delete content of @lcl_TableHI */
delete @lcl_TableHI

/* read next index record */
select top 1 @Current_Index_name = Index_name, @CurrentIndexKeys = IndexKeys
from #dm_lcl_HelpIndex
where Index_name > @Current_Index_name
order by Index_name
end

CleanUp:
return @RetCode

end

MohammedU
Posting Yak Master

145 Posts

Posted - 2006-12-24 : 18:27:09

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18548

MohammedU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources