--********************** CONSULTA DE COLUMNAS INDEXADAS *******************************
SELECT
--TableName = t.name,
IndexName = ind.name,
--IndexId = ind.index_id,
--ColumnId = ic.index_column_id,
ColumnName = col.name
--ind.*,
--ic.*,
--col.*
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
AND t.object_id = OBJECT_ID(@NAME_TABLE)
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
--********************** CONSULTA DE COLUMNAS *******************************
SELECT
c.name 'Columna',
t.Name 'Tipo',
c.max_length 'Tamaño',
--c.precision ,
--c.scale ,
CASE WHEN c.is_nullable = 0 THEN 'N' ELSE 'Y' END 'Nulo',
CASE WHEN ISNULL(i.is_primary_key, 0) = 0 THEN 'N' ELSE 'Y' END 'PK' ,
i.object_id, c.column_id,
CASE WHEN (SELECT COUNT(1) FROM sys.foreign_key_columns WHERE parent_object_id = i.object_id
AND parent_column_id = c.column_id ) > 0 THEN 'Y'
ELSE 'N' END 'FK'
,PP.VALUE 'DESCRIPCION'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.extended_properties PP ON PP.major_id = C.object_id AND PP.minor_id = C.column_id
WHERE
c.object_id = OBJECT_ID(@NAME_TABLE)
Resultado:
