Obtener los indices y las columnas que componen dichos indices

[yera2002]

Hola a todos:

Espero alguien me pueda ayudar con la sentencia SQL que me permita saber que indices existen en la base de datos y cuales son las columnas que componen dichos indices (en caso de que sean indices compuestos)

Gracias

[iislas]

Código SQL:

DROP TABLE #tables
DECLARE @tabla sysname
DECLARE @record INT
CREATE TABLE #tables (id INT IDENTITY PRIMARY KEY, name sysname)
INSERT INTO #tables (name)
SELECT name FROM sysobjects WHERE TYPE='U' ORDER BY name
SET @record = 1
WHILE @record <= (SELECT MAX(id) FROM #tables)
BEGIN
SELECT @tabla = name FROM #tables WHERE id = @record
SELECT @tabla
EXEC sp_helpindex @tabla
SET @record = @record + 1
END
GO
 

[matanga]

Esta consulta es más para confirmar y ver si es correcta que para responder el post, el compañero iislas tiene más experiencia.

Código:

SELECT 
object_name(cols.object_id) tabla
,cols.name columna
,ind.name indice
,ind.type_desc tipo
,ind.is_unique 
FROM 
sys.columns cols, sys.indexes ind , sys.index_columns ind_cols
where 
cols.object_id = ind.object_id
and cols.object_id = ind_cols.object_id
and cols.column_id = ind_cols.column_id
and ind.index_id = ind_cols.index_id
order by object_name(cols.object_id), ind.name

Saludos

[yera2002]

Hola:

Gracias a ambos por constestar. De hecho ambas opciones son buenas...lo unico que me queda duda es con la respuesta de matanga obtengo un muy buen resultado, sin embargo he estado analizando la informacion resultante y tengo un ejemplo de algo que me pasa y no sé por que.

tengo una tabla TABLA1 la cual contiene indices creados mediante la sugerencia de tunneo. Ahora bien esta tabla cuenta con 30 campos y el indice que yo veo me incluye como 5, sin embargo la consulta que me mandas me pone alrededor de 25 campos por ese indice

¿Que puede ser?

[yera2002]

O si bien saben como acceder via recordset a la informacion que me envia el comando exec sp_help 'tabla1'

Saludos...

[iislas]

Guarda el contenido de lo que te envia el sp_helpindex

Código SQL:

DROP TABLE #tables
DROP TABLE #indices
DECLARE @tabla sysname
DECLARE @record INT
CREATE TABLE #tables (id INT IDENTITY PRIMARY KEY, name sysname)
CREATE TABLE #indices (tabla VARCHAR(50), index_name sysname, index_description VARCHAR(50), index_keys VARCHAR(255))
INSERT INTO #tables (name)
SELECT name FROM sysobjects WHERE TYPE='U' ORDER BY name
SET @record = 1
WHILE @record <= (SELECT MAX(id) FROM #tables)
BEGIN
SELECT @tabla = name FROM #tables WHERE id = @record
INSERT INTO #indices (index_name, index_description, index_keys)
EXEC sp_helpindex @tabla
UPDATE #indices SET [tabla] = @tabla
SET @record = @record + 1
END
GO
 
-- al final solo haces un select a la tabla #indices
SELECT * FROM #indices
 

[yera2002]

Hola IIslas:

Muchas gracias por contestar, en efecto el procedimiento que me enviaste funciona, salvo que tiene el detalle que en valor de tabla pone siempre el ultimo registro encontrado. Asi que me di a la tarea de seguir buscando, en base a lo que tu me escribiste y lo que encontre fue esto:

DECLARE @IndexInfoTemp TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)

DECLARE @IndexInfo TABLE (table_name sysname
,index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)

DECLARE @Tables Table (RowID int not null identity(1,1)
,TableName sysname
)
DECLARE @MaxRow int
DECLARE @CurrentRow int
DECLARE @CurrentTable sysname

INSERT INTO @Tables
SELECT
DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1

WHILE @CurrentRow<=@MaxRow
BEGIN
SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow
INSERT INTO @IndexInfoTemp
exec sp_helpindex @CurrentTable
INSERT INTO @IndexInfo
(table_name , index_name , index_description , index_keys)
SELECT
@CurrentTable , index_name , index_description , index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow+1
END --WHILE

SELECT table_name AS [Table] , index_keys AS [ColumnName],index_name,index_description
FROM @IndexInfo

Funciona a la perfeccion por lo menos para lo que yo estaba buscando, de verdad gracias

Saludos...