index details

[ravi]

Hi,
I need to get the index_name,colum_name and table_name in entire database.
Coule you let me know how I can get that details.
Thanks,

[rmiao]

Try sp_msforeachtable 'sp_helpindex "?"'

or query sysindexes.

[ravi]

rmiao ,
It will not give give me all the details in single row.

I'm looking like INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Thanks,
Ravi

[nosepicker]

Here is a query that I got from another forum. See if this will suit your needs (although I'm not sure if this query's criteria for 'Clustered' vs. 'Non-Clustered' is entirely accurate):

SELECT LEFT(so.[name], 30) TableName, LEFT(si.[name], 50) IndexName, LEFT(sc.[name], 30) ColumnName, si.indid,
CASE si.indid WHEN 1 THEN 'Clustered' ELSE 'Non-Clustered' END IndexType, LEFT(sfg.groupname, 10) GroupName
FROM sysindexes si
INNER JOIN sysindexkeys sik ON (si.[id] = sik.[id] AND si.indid = sik.indid)
INNER JOIN sysobjects so ON si.[id] = so.[id]
INNER JOIN syscolumns sc ON (so.[id] = sc.[id] AND sik.colid = sc.colid)
INNER JOIN sysfilegroups sfg ON si.groupid = sfg.groupid
--where si.indid between 2 and 254 --only non-clustered indexes
--WHERE si.indid = 1 --only clustered indexes
WHERE si.[name] NOT LIKE 'sys%' --filter out tables that start with sys
AND si.[name] NOT LIKE '[u,n]c%sys%' --filter out tables that start with ncsys, nc1, nc2, ucsys
AND si.[name] NOT LIKE '_WA_%' --filter out tables that start with _WA_
AND si.[name] NOT LIKE 'hind_%' --filter out tables that start with hind_
AND so.[name] <> 'dtproperties' --filter out the dtproperties table
AND sfg.groupname = 'PRIMARY'
ORDER BY so.[name], si.indid

[MAK]

--Create this procedure on the database

Create proc showindex @tablename varchar(128)
as
print replace(replace(@tablename,'[dbo].[',''),']','')
-- Declare variables to use in this example.
DECLARE @id int, @type char(2),@msg varchar(80),
@indid smallint, @indname sysname, @status int,
@indkey int, @name varchar(30)
-- Obtain the identification number for the authors table to look up
-- its indexes in the sysindexes table.
SET NOCOUNT ON
SELECT @id = id, @type = type
FROM sysobjects
WHERE name = replace(replace(@tablename,'[dbo].[',''),']','') and type = 'U'

-- Start printing the output information.

-- Loop through all indexes in the authors table.
-- Declare a cursor.
DECLARE i cursor
FOR
SELECT indid, name, status
FROM sysindexes
WHERE id = @id

-- Open the cursor and fetch next set of index information.
OPEN i

FETCH NEXT FROM i INTO @indid, @indname, @status

IF @@FETCH_STATUS = 0
PRINT ' '

-- While there are still rows to retrieve from the cursor,
-- find out index information and print it.
WHILE @@FETCH_STATUS = 0
BEGIN

SET @msg = NULL
-- Print the index name and the index number.
SET @msg = ' Index number ' + CONVERT(varchar, @indid)+
' is '+@indname

SET @indkey = 1
-- @indkey (equivalent to key_id in the syntax diagram of
-- INDEX_COL) can be from 1 to 16.
WHILE @indkey <= 16 and INDEX_COL(@name, @indid, @indkey)
IS NOT NULL

BEGIN
-- Print different information if @indkey <> 1.
IF @indkey = 1
SET @msg = @msg + ' on '
+ index_col(@name, @indid, @indkey)
ELSE
SET @msg = @msg + ', '
+ index_col(@name, @indid, @indkey)

SET @indkey = @indkey + 1
END

PRINT @msg
SET @msg = NULL
FETCH NEXT FROM i INTO @indid, @indname, @status

END
CLOSE i
DEALLOCATE i

SET NOCOUNT OFF
go

[MAK]

--THen try

sp_msforeachtable "exec databasename..showindex '?'"

[ravi]

Thanks nosepicker,
This what I'm looking for.

I appreciate your help.

[ravi]

Thanks nosepicker,
This what I'm looking for.

I appreciate your help.