Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
Thanks
Hi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks
|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:

> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>
|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2, IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,In dCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegNa me,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,I ndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:

> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>