-
Notifications
You must be signed in to change notification settings - Fork 23
/
enum_indexes.sql
115 lines (95 loc) · 3.7 KB
/
enum_indexes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
--****************************************************************************************
-- List index information for all databases
--****************************************************************************************
-- Version: 1.0
-- Author: Theo Ekelmans
-- Email: [email protected]
-- Date: 2005-10-07
--****************************************************************************************
use master
DECLARE @db_name varchar(128)
DECLARE @DbID int
DECLARE @sql_string nvarchar(4000)
set nocount on
CREATE TABLE [#tblHistoryIndex] (
[DbName] [varchar] (128) NOT NULL ,
[TableName] [varchar] (128) NOT NULL ,
[IndexName] [varchar] (128) NOT NULL ,
[Indexid] [int] NOT NULL ,
[Primary] [int] NULL ,
[Clustered] [int] NULL ,
[Unique] [int] NULL ,
[IgnoreDupKey] [int] NULL ,
[IgnoreDupRow] [int] NULL ,
[NoRecompute] [int] NULL ,
[FillFactor] [int] NULL ,
[EstRowCount] [bigint] NULL ,
[ReservedKB] [bigint] NULL ,
[UsedKB] [bigint] NULL ,
[KeyNumber] [int] NULL ,
[ColumnName] [varchar] (128) NULL ,
[DataType] [varchar] (128) NULL ,
[Precision] [int] NULL ,
[Scale] [int] NULL ,
[IsComputed] [int] NULL ,
[IsNullable] [int] NULL ,
[Collation] [varchar] (128) NULL )
declare db_cursor cursor forward_only for
SELECT name, DbID
FROM master..sysdatabases
WHERE name NOT IN ('northwind', 'pubs')
AND (status & 32) <> 32 --loading.
AND (status & 64) <> 64 --pre recovery.
AND (status & 128) <> 128 --recovering.
AND (status & 256) <> 256 --not recovered.
AND (status & 512) <> 512 --Offline
AND (status & 32768) <> 32768 --emergency mode.
AND DbID > 4
open db_cursor
fetch next from db_cursor into @db_name, @DbID
while @@FETCH_STATUS = 0
begin
set @sql_string = ''
+' Insert into #tblHistoryIndex '
+' select ''' + @db_name + ''' as ''DbName'', '
+' o.name as ''TableName'', '
+' i.name as ''IndexName'', '
+' i.indid as ''Indexid'', '
+' CASE WHEN (i.status & 0x800) = 0 THEN 0 ELSE 1 END AS ''Primary'', '
+' CASE WHEN (i.status & 0x10) = 0 THEN 0 ELSE 1 END AS ''Clustered'', '
+' CASE WHEN (i.status & 0x2) = 0 THEN 0 ELSE 1 END AS ''Unique'', '
+' CASE WHEN (i.status & 0x1) = 0 THEN 0 ELSE 1 END AS ''IgnoreDupKey'', '
+' CASE WHEN (i.status & 0x4) = 0 THEN 0 ELSE 1 END AS ''IgnoreDupRow'', '
+' CASE WHEN (i.status & 0x1000000) = 0 THEN 0 ELSE 1 END AS ''NoRecompute'', '
+' i.OrigFillFactor AS ''FillFactor'', '
+' i.rowcnt as ''EstRowCount'', '
+' i.reserved * cast(8 as bigint) as ''ReservedKB'', '
+' i.used * cast(8 as bigint) as ''UsedKB'', '
+' k.keyno as ''KeyNumber'', '
+' c.name as ''ColumnName'', '
+' t.name as ''DataType'', '
+' c.xprec as ''Precision'', '
+' c.xscale as ''Scale'', '
+' c.iscomputed as ''IsComputed'', '
+' c.isnullable as ''IsNullable'', '
+' c.collation as ''Collation'' '
+' '
+' from [' + @db_name + ']..sysobjects o with(nolock) '
+' inner join [' + @db_name + ']..sysindexes i with(nolock) on o.id = i.id '
+' inner join [' + @db_name + ']..sysindexkeys k with(nolock) on i.id = k.id and i.indid = k.indid '
+' inner join [' + @db_name + ']..syscolumns c with(nolock) on k.id = c.id and k.colid = c.colid '
+' inner join [' + @db_name + ']..systypes t with(nolock) on c.xtype = t.xtype '
+' '
+' where o.xtype <> ''S'' ' -- Ignore system objects
+' and i.name not like ''_wa_sys_%'' ' -- Ignore statistics
+' '
+' order by '
+' o.name, '
+' k.indid, '
+' k.keyno '
execute sp_executesql @sql_string
fetch next from db_cursor into @db_name, @DbID
end
deallocate db_cursor
select * from #tblHistoryIndex
drop table #tblHistoryIndex