January 10, 2007 at 8:09 am
I am trying to use the system views to gather the index information. I need the table, index name, index type, columns, column order. In looking at the system views, I cannot figure out how to connect the indexs to the table and to get the correct column information.
What I am trying to do is take a database of empty tables and correct the naming of indexes to a standardized format. I need to read the index information, delete the index, then rebuild it using the correct naming conventions. I am trying to automate it because of the number of tables in the databases that I am dealing with.
I was able to get the SP written to take care of the default constraints, but this one has not come together.
Any help with this is appreciated. TIA.
January 10, 2007 at 11:36 am
I don't have the script handy but you can use the following table get the information...
select * from sys.tables
select * from sys.indexes
sp_helpindex [schema.tablename]
see BOL for more details...
MohammedU
Microsoft SQL Server MVP
January 10, 2007 at 12:19 pm
I found an obscure reference that allowed me to do what I wanted.
Unfortunately, I tried the tables you suggested, but you cannot get the information about the columns through it. It takes a pretty specialized query to do it.
The following will get you a list of the tables, index names, index type and up to 10 columns that are in the index. (I have 8 columns as a maximm in the tables I am dealing with, so I did not exted it beyond 10.)
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER
procedure [dbo].[p_help_db_indexes]
AS
declare
@empty varchar(1)
select
@empty = ''
IF
EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ttblIndexListing]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[ttblIndexListing]
END
-- 35 is the lenght of the name field of the master.dbo.spt_values table
declare
@IgnoreDuplicateKeys varchar(35),
@Unique
varchar(35),
@IgnoreDuplicateRows
varchar(35),
@Clustered
varchar(35),
@Hypotethical
varchar(35),
@Statistics
varchar(35),
varchar(35),
@UniqueKey
varchar(35),
@AutoCreate
varchar(35),
@StatsNoRecompute
varchar(35)
select
@IgnoreDuplicateKeys = name from master.dbo.spt_values
where type = 'I' and number = 1 --ignore duplicate keys
select
@Unique = name from master.dbo.spt_values
where type = 'I' and number = 2 --unique
select
@IgnoreDuplicateRows = name from master.dbo.spt_values
where type = 'I' and number = 4 --ignore duplicate rows
select
@Clustered = name from master.dbo.spt_values
where type = 'I' and number = 16 --clustered
select
@Hypotethical = name from master.dbo.spt_values
where type = 'I' and number = 32 --hypotethical
select
@Statistics = name from master.dbo.spt_values
where type = 'I' and number = 64 --statistics
select
@primarykey-2 = name from master.dbo.spt_values
where type = 'I' and number = 2048 --primary key
select
@UniqueKey = name from master.dbo.spt_values
where type = 'I' and number = 4096 --unique key
select
@AutoCreate = name from master.dbo.spt_values
where type = 'I' and number = 8388608 --auto create
select
@StatsNoRecompute = name from master.dbo.spt_values
where type = 'I' and number = 16777216 --stats no recompute
select
o.name AS TableName,
i
.name AS IndexName,
'ClusteredIndex' = case when (i.status & 16)<>0 then @Clustered else 'non'+@Clustered end,
'UniqueIndex' = case when (i.status & 2)<>0 then @Unique else @empty end,
'PrimaryKeyFlag' = case when (i.status & 2048)<>0 then @primarykey-2 else @empty end,
'UniqueKeyFlag' = case when (i.status & 4096)<>0 then @UniqueKey else @empty end,
'IndexDescription' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on
case when (i.status & 16)<>0 then @Clustered else 'non'+@Clustered end
+ case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end
+ case when (i.status & 2)<>0 then ', '+@Unique else @empty end
+ case when (i.status & 4)<>0 then ', '+@IgnoreDuplicateRows else @empty end
+ case when (i.status & 64)<>0 then ', '+@Statistics else
case when (i.status & 32)<>0 then ', '+@Hypotethical else @empty end end
+ case when (i.status & 2048)<>0 then ', '+@primarykey-2 else @empty end
+ case when (i.status & 4096)<>0 then ', '+@UniqueKey else @empty end
+ case when (i.status & 8388608)<>0 then ', '+@AutoCreate else @empty end
+ case when (i.status & 16777216)<>0 then ', '+@StatsNoRecompute else @empty end),
'IndexColumn1' = index_col(o.name,indid, 1),
'IndexColumn2' = index_col(o.name,indid, 2),
'IndexColumn3' = index_col(o.name,indid, 3),
'IndexColumn4' = index_col(o.name,indid, 4),
'IndexColumn5' = index_col(o.name,indid, 5),
'IndexColumn6' = index_col(o.name,indid, 6),
'IndexColumn7' = index_col(o.name,indid, 7),
'IndexColumn8' = index_col(o.name,indid, 8),
'IndexColumn9' = index_col(o.name,indid, 9),
'IndexColumn10' = index_col(o.name,indid, 10)
INTO
[dbo].[ttblIndexListing]
from
sysindexes i, sysobjects o
where
i.id = o.id and
indid
> 0 and indid < 255 --all the clustered (=1), non clusterd (>1 and <251), and text or image (=255)
and o.type = 'U' --user table
--ignore the indexes for the autostat
and (i.status & 64) = 0 --index with duplicates
and (i.status & 8388608) = 0 --auto created index
and (i.status & 16777216)= 0 --stats no recompute
order by o.name
January 10, 2007 at 1:01 pm
something like this might help you; note i limited it to top 100 because this can be a huge result set on some db's: the columns in the sqlstatmenet are in the correct order (1-16)
typical results:
| tablename | indexname | sqlstatement |
| WEBFUNDSREQUEST | PK__WEBFUNDSREQUEST__0B7F0D80 | CREATE UNIQUE CLUSTERED INDEX [PK__WEBFUNDSREQUEST__0B7F0D80] ON [WEBFUNDSREQUEST] (WEBFUNDSREQUESTTBLKEY) WITH FILLFACTOR = 90 |
| PPEB | PK__PPEB__470850F4 | CREATE UNIQUE CLUSTERED INDEX [PK__PPEB__470850F4] ON [PPEB] (EBTBLKEY) WITH FILLFACTOR = 90 |
| SFLNCOND | PK__SFLNCOND__013F142A | CREATE UNIQUE CLUSTERED INDEX [PK__SFLNCOND__013F142A] ON [SFLNCOND] (LOANCONDTBLKEY) WITH FILLFACTOR = 90 |
| SFLNCOND | UQLOANCOND | CREATE UNIQUE INDEX [UQLOANCOND] ON [SFLNCOND] (SFHEADTBLKEY,LOANCONDNBR,CONDITIONNAME) WITH FILLFACTOR = 90 |
| PPTCALLOCBLDYR | PK__PPTCALLOCBLDYR__0140AAD8 | CREATE UNIQUE CLUSTERED INDEX [PK__PPTCALLOCBLDYR__0140AAD8] ON [PPTCALLOCBLDYR] (TCALLOCBLDYRTBLKEY) WITH FILLFACTOR = 90 |
SELECT TOP 100
REPLICATE(' ',4000) AS COLNAMES ,
OBJECT_NAME(I.ID) AS TABLENAME,
I.ID AS TABLEID,
I.INDID AS INDEXID,
I.NAME AS INDEXNAME,
I.STATUS,
INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
INTO #TMP
FROM SYSINDEXES I
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
--AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0
--AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0
DECLARE
@ISQL VARCHAR(4000),
@TABLEID INT,
@INDEXID INT,
@MAXTABLELENGTH INT,
@MAXINDEXLENGTH INT
--USED FOR FORMATTING ONLY
SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP
DECLARE C1 CURSOR FOR
SELECT TABLEID,INDEXID FROM #TMP
OPEN C1
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @ISQL = ''
SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
AND I.ID=@TABLEID AND I.INDID=@INDEXID
ORDER BY SYSCOLUMNS.COLID
UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
END
CLOSE C1
DEALLOCATE C1
--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)
SELECT TABLENAME,INDEXNAME,'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END
+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END
+ ' INDEX [' + UPPER(INDEXNAME) + ']'
+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
+' ON [' + UPPER(TABLENAME) + '] '
+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))
+ '(' + UPPER(COLNAMES) + ')'
+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END --AS SQL
FROM #TMP
--SELECT * FROM #TMP
DROP TABLE #TMP
Lowell
January 11, 2007 at 11:58 am
DBArtisan from Embarcadero has the tools you require. Unfortunately I don't have it on my system to tell you exactly what SP's it is using etc.
But it is able to report every index on every table in a database in one tablulated result which you can then save in a multitude of formats.
Swaroop
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply