sql table index (SQL2005)

Sk8tzz

Hi. I want to retrieve the list of indexes against a table, and the fields in the index. Also if posible the ordinal of that index. I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL. Does anyone know the query or queries to retrieve this information, please post it here. Also performance is an issue, so this info must be retrieved fast. :->:->:-> .NET Rules

Colin Angus Mackay

Sk8tzz wrote:

I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL.

sp_helpindex is using TSQL. I suspect you mean you don't want to use a stored procedure? You may want to take a look at the sysindexes[^] table. This will be the fastest way to retrieve the information you want - however, you'll have to join it to some of the other system tables to get the names of the columns and so on.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog

Sk8tzz

I know that, I looked at that sp and I'm still fuzzy about what exactly its doing. Is there a simpler way of quering this info. :~ .NET Rules

Eric Dahlvang

Why not use sp_helpindex?

DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor

CREATE TABLE #TempIndexes (table_name varchar(255),index_name varchar(255),index_description varchar(255),index_keys nvarchar(2126))
CREATE TABLE #TempHoldIndexes (index_name varchar(255),index_description varchar(255),index_keys nvarchar(2126))

DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
INSERT #TempHoldIndexes EXEC sp_helpindex @tablename

INSERT #TempIndexes SELECT @tablename, index\_name,index\_description,index\_keys FROM #TempHoldIndexes
DELETE FROM #TempHoldIndexes

END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

SELECT * FROM #TempIndexes
DROP TABLE #TempIndexes
DROP TABLE #TempHoldIndexes

---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin

Eric Dahlvang

Or, here is a modified version of sp_helpindex that you can run from the query analyzer:

-- create temp table
create table #spindtab
(
table_name sysname,
index_name sysname collate database_default NOT NULL,
stats int,
groupname sysname collate database_default NOT NULL,
index_keys nvarchar(2126) collate database_default NOT NULL -- see @keys above for length descr
)

DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor

declare @objname nvarchar(776) -- the table to check for indexes
FETCH NEXT FROM tnames_cursor INTO @objname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @objname = RTRIM(@objname)
-- PRELIM
set nocount on

declare @objid int,			-- the object id of the table
		@indid smallint,	-- the index id of an index
		@groupid smallint,  -- the filegroup id of an index
		@indname sysname,
		@groupname sysname,
		@status int,
		@keys nvarchar(2126),	--Length (16\*max\_identifierLength)+(15\*2)+(16\*3)
		@dbname	sysname

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db\_name()
begin
		raiserror(15250,-1,-1)
		--return (1)
end

-- Check to see the the table exists and initialize @objid.
select @objid = object\_id(@objname)
if @objid is NULL
begin
	select @dbname=db\_name()
	raiserror(15009,-1,-1,@objname,@dbname)
	--return (1)
end

-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh\_51196)
declare ms\_crs\_ind cursor local static for
	select indid, groupid, name, status from sysindexes
		where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
open ms\_crs\_ind
fetch ms\_crs\_ind into @indid, @groupid, @indname, @status

-- IF NO INDEX, QUIT
if @@fetch\_status < 0
begin
	deallocate ms\_crs\_ind
	--raiserror(15472,-1,-1) --'Object does not have any indexes.'
	insert into #spindtab values (@objname, '', '', '','')
	CONTINUE
	--return (0)
end



-- Now check out each index, figure out its type and keys and
--	save the info in a temporary table that we'll print out at the end.
while @@fetch\_status >= 0
begin
	-- First we'll figure out what the keys are.
	declare @i int, @thiskey nvarchar(131) -- 128+3

	select @keys = index\_col(@objname, @indid, 1), @i = 2
	if (indexkey\_property(@objid, @indid, 1, 'isdescending') = 1)
		select @keys = @keys  + '(-)'

	sel

Sk8tzz

Thanks, will test, trying to write a unique little DAL that does not require any generated code, strongly typed as well. :cool: .NET Rules