-
Notifications
You must be signed in to change notification settings - Fork 1
/
dr14Fix.sql
67 lines (53 loc) · 1.55 KB
/
dr14Fix.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
-- The following two queries return information about
-- which objects belongs to which filegroup
--insert suedb.dbo.indexInfo (TableName, IndexName, IndexID, IndexType, sourceFG)
SELECT distinct
OBJECT_NAME(i.[object_id]) AS [ObjectName]
,i.[name] AS [IndexName]
,i.[index_id] AS [IndexID]
,i.[type_desc] AS [IndexType]
--,i.[data_space_id] AS [DatabaseSpaceID]
,f.[name] AS [FileGroup]
-- ,d.[physical_name] AS [DatabaseFileName]
--,i.type
--into suedb.dbo.IndexPrimary
FROM [sys].[indexes] i
INNER JOIN [sys].[filegroups] f
ON f.[data_space_id] = i.[data_space_id]
INNER JOIN [sys].[database_files] d
ON f.[data_space_id] = d.[data_space_id]
INNER JOIN [sys].[data_spaces] s
ON f.[data_space_id] = s.[data_space_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
--and i.type = 5
--and i.index_id > 0
and f.name = 'PRIMARY'
ORDER BY
--OBJECT_NAME(i.[object_id]),
f.[name]
-- ,i.[data_space_id]
GO
update t2
--set FileGroup='DATAFG'
set compression='PAGE'
--select t1.objectName, t2.*
from suedb.dbo.IndexPrimary t1
join IndexMapFG t2
on t1.ObjectName = t2.TableName
where t2.code='K'
--and t2.indexGroup not like 'FINISH'
and t2.TableName != 'SpecObjAll'
--order by t1.ObjectName
select t1.objectName, t2.*
from suedb.dbo.IndexPrimary t1
join IndexMapFG t2
on t1.ObjectName = t2.TableName
where t2.code='K'
--and t2.indexGroup not like 'FINISH'
and t2.TableName != 'SpecObjAll'
order by t1.ObjectName
select * from suedb.dbo.indexPrimary
update IndexMapFG
set FileGroup='SPEC'
where TableName='SpecPhotoAll'
and code = 'K'