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
|
CREATE Procedure [dbo].[up_DropClusteredIndex] (
@DB_NAME sysname,
@SchemaNameTableName sysname)
AS
--
/*
http://msdn.microsoft.com/en-us/library/ms176118.aspx
The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints.
To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.
--------- i.is_primary_key 0, i.is_unique 1 , i.is_unique_constraint 0 ---------
DROP INDEX [IX_Table2] ON [dbo].[Table2] WITH ( ONLINE = OFF )
--------- i.is_primary_key 1, i.is_unique 1 , i.is_unique_constraint 0 -----------
ALTER TABLE [dbo].[Table2] DROP CONSTRAINT [PK_Table2]
http://msdn.microsoft.com/fr-fr/library/ms177623.aspx
sys.stats
SELECT i.name AS indexname, i.is_primary_key, i.is_unique, i.is_unique_constraint, t.name AS tablename, st.no_recompute
FROM sys.tables AS t INNER JOIN
sys.indexes AS i ON t.object_id = i.object_id INNER JOIN
sys.stats AS st ON i.object_id = st.object_id
*/
BEGIN
SET NOCOUNT ON
DECLARE @SQL1 VARCHAR(1000) -- contient la requete pour chercher l'index
DECLARE @SCH_NAME VARCHAR(100)
DECLARE @TABLE_NAME VARCHAR(100)
-- info sur l'index CLUSTERED
DECLARE @INDEX_NAME VARCHAR(100)
DECLARE @IS_PK BIT
DECLARE @IS_UNIQUE BIT
DECLARE @IS_UNIQUE_CONSTRAINT BIT
-- Tables temporaires
DECLARE @STORE_CLUST_INDEX TABLE (INDEXNAME varchar(100),
IS_PK bit,
IS_UNIQUE bit,
IS_UNIQUE_CONSTRAINT bit,
ALLOW_ROW_LOCKS bit,
ALLOW_PAGE_LOCKS bit)
SET @SCH_NAME = SUBSTRING(@SchemaNameTableName, 1, CHARINDEX('.',@SchemaNameTableName)-1)
SET @TABLE_NAME = SUBSTRING(@SchemaNameTableName, CHARINDEX('.',@SchemaNameTableName)+1, 200)
-- Création de la requete pour recuperer des infos sur l'eventuel index CLUSTERED
SET @SQL1 = 'USE '+@DB_NAME+ ';
SELECT i.name, i.is_primary_key, i.is_unique, i.is_unique_constraint, i.allow_row_locks, i.allow_page_locks
FROM sys.tables AS t INNER JOIN
sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN
sys.indexes AS i ON i.object_id = t.object_id
WHERE (i.type = 1)
AND (i.index_id = 1)
AND (s.name = '''+@sch_NAME+''')
AND (t.name ='''+@TABLE_NAME+''')'
-- Insere dans la table temporaire
INSERT INTO @STORE_CLUST_INDEX EXEC (@SQL1)
-- Verifie s'il y a un index
IF (SELECT COUNT(*) FROM @STORE_CLUST_INDEX) = 1
BEGIN
-- Recupere les infos de l'index
SELECT @INDEX_NAME=INDEXNAME, @IS_UNIQUE=IS_UNIQUE, @IS_PK=IS_PK, @IS_UNIQUE_CONSTRAINT=IS_UNIQUE_CONSTRAINT
FROM @STORE_CLUST_INDEX
--DEBUG
--PRINT @INDEX_NAME
-- Selon le type d'index le statment est different
IF (@IS_PK = 1) OR (@IS_UNIQUE_CONSTRAINT = 1)
BEGIN
-- ALTER TABLE
SET @SQL1= 'USE ' + @DB_NAME + '; ALTER TABLE ' + @SchemaNameTableName + ' DROP CONSTRAINT ' + @INDEX_NAME
PRINT @SQL1
EXEC (@SQL1)
END
ELSE
BEGIN
-- DROP INDEX
SET @SQL1= 'USE ' + @DB_NAME + '; DROP INDEX ' + @INDEX_NAME + ' ON ' + @SchemaNameTableName + ' WITH ( ONLINE = OFF )'
PRINT @SQL1
EXEC (@SQL1)
END
END
END |
Activation / désaction index et dead lock
Répondre avec citation
Envoyé par cpdump
Partager