Bi0KZNHX

1/*this script will find all the heaps in the database that have the columns you specify, then index them on those columns and write a record to jm_clusterize
2    It was written for sql server enterprise edition, taking advantage of online indexing, so if you have trouble, get rid of 'with (online = on)
3    You should run this script after hours. It will *try* to do the index online, but if it can't, it will do the index offline, which will lock the table for a
4    few seconds/minutes
5    */
6
7    declare @table varchar(255),
8    @sqlcreate nvarchar(max),
9    @sqlDrop nvarchar(max),
10    @column1 varchar(255),
11    @column2 varchar(255),
12    @column3 varchar(255)
13
14    /*specify the coulumns you want to cluster on here. You always want to cluster on the create timestamp first, and nextgen is pretty good 
15    at having a create_timestamp on every table, but you will want to cluster some tables on seq_no, or uniq_id, then patient demographics tables will need to
16    use person_id first. I'm on the fence on clustering on 3 columns - it may be faster, it may be slower. 
17
18    */
19    set @column1 = 'create_timestamp'
20    set @column2 = 'enc_id'
21    --set @column3 = 'seq_no'
22
23    --drop table jm_clusterize
24    if not exists (select name from sys.objects where name = 'jm_clusterize')
25    begin
26    create table jm_clusterize (table_name varchar(255), column1 varchar(255), column2 varchar(255), column3 varchar(255), create_statement nvarchar(max),
27    drop_statement nvarchar(max), create_timestamp datetime, modify_timestamp datetime)
28    end
29
30
31    declare c cursor for
32
33    --This is the query that will show you all the heaps that could be clustered on the columns you pick
34    SELECT TBL.name AS TableName
35    FROM sys.tables AS TBL 
36         INNER JOIN sys.schemas AS SCH
37             ON TBL.schema_id = SCH.schema_id 
38         INNER JOIN sys.indexes AS IDX
39             ON TBL.object_id = IDX.object_id 
40                AND IDX.type = 0 -- = Heap 
41                inner join sys.columns sc1 on tbl.object_id = sc1.object_id
42                inner join sys.columns sc2 on tbl.object_id = sc2.object_id
43                --inner join sys.columns sc3 on tbl.object_id = sc3.object_id
44                INNER JOIN 
45        sys.partitions p ON idx.object_id = p.OBJECT_ID AND idx.index_id = p.index_id
46    INNER JOIN 
47        sys.allocation_units a ON p.partition_id = a.container_id
48        where sc1.name = @column1
49        and  sc2.name = @column2
50        --and sc3.name = @column3
51        group by TBL.name
52    ORDER BY SUM(a.total_pages) desc, TableName
53
54
55    open c
56
57    fetch next from c into @table
58
59    while @@fetch_status = 0 
60    begin
61
62    set @sqlcreate = 'begin try create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
63    + @column2 
64    --+ ',' + @column3 
65    + ') with (online = on) end try
66    begin catch create clustered index inx_jm_clstr on ' + @table + ' (' + @column1 + ','
67    + @column2
68    --+ ','+ @column3 
69    + ') end catch'
70
71    set @sqlDrop = 'drop index inx_jm_clstr on ' + @table
72
73    insert into jm_clusterize (table_name, column1, column2, column3, create_statement, drop_statement, create_timestamp, modify_timestamp)
74
75    select @table, @column1, @column2, @column3, @sqlcreate, @sqlDrop, current_timestamp, current_timestamp
76
77    --comment out the line below and it won't actually create the index
78    exec sp_executesql @sqlcreate
79
80
81    fetch next from c into @table
82    end
83
84    close c
85
86    deallocate c
87
88    select * from jm_clusterize
89    where column1 = @column1
90    and column2 = @column2
91    and column3 = @column3