SQL Server - La FAQConsultez toutes les FAQ

1
2
3
DECLARE @datestring varchar(8) 
SET @datestring = '12/21/98' 
SELECT ISDATE(@datestring)

1
2
3
4
USE pubs 
SELECT ISNUMERIC(zip)  
FROM authors 
GO

SET XACT_ABORT ON

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select OBJECT_NAME(IUS.object_id), I.name, IUS.user_seeks, IUS.user_scans, IUS.user_lookups, IUS.user_updates,  
       IUS.user_seeks + IUS.user_scans + IUS.user_lookups AS Total_use,  
       T.user_seeks + T.user_scans + T.user_lookups AS Table_use,  
       (CAST(IUS.user_seeks + IUS.user_scans + IUS.user_lookups AS FLOAT) /  
        NULLIF(CAST(T.user_seeks + T.user_scans + T.user_lookups AS FLOAT), 0)) * 100 AS efficacite_percent  
from   sys.dm_db_index_usage_stats AS IUS  
       INNER JOIN sys.indexes AS I  
             ON IUS.object_id = I.object_id  
                AND IUS.index_id = I.index_id    
       INNER JOIN sys.dm_db_index_usage_stats AS T  
             ON IUS.object_id = T.object_id  
                AND T.index_id IN (0, 1)  
where  IUS.database_id = DB_ID()  
  AND  I.name NOT IN (SELECT CONSTRAINT_NAME   
                      FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS)  
ORDER  BY efficacite_percent

1
2
3
4
5
6
7
8
9
10
11
select 'DROP INDEX [' + I.name +'] ON [' + SCHEMA_NAME(o.schema_id) + '].['+ OBJECT_NAME(IUS.object_id) +'];'  
from   sys.dm_db_index_usage_stats AS IUS  
       INNER JOIN sys.objects AS o  
             ON IUS.object_id = o.object_id   
       INNER JOIN sys.indexes AS I   
             ON IUS.object_id = I.object_id   
                AND IUS.index_id = I.index_id     
where  database_id = DB_ID()   
  AND  user_seeks + user_scans + user_lookups = 0   
  AND  I.name NOT IN (SELECT CONSTRAINT_NAME    
                      FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS)

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
WITH  
T_IDX AS (SELECT TABLE_SCHEMA, TABLE_NAME, i.name AS INDEX_NAME,   
                 c.name AS COLUMN_NAME, key_ordinal AS ORDINAL_POSITION  
          FROM   sys.indexes AS i  
                 INNER JOIN sys.objects AS o  
                       ON i.object_id = o.object_id  
                 INNER JOIN sys.schemas AS s  
                       ON o.schema_id = s.schema_id  
                 INNER JOIN INFORMATION_SCHEMA.TABLES AS T  
                       ON s.name = T.TABLE_SCHEMA AND o.name = TABLE_NAME  
                 INNER JOIN sys.index_columns AS ic  
                       ON i.object_id = ic.object_id  
                          AND i.index_id = ic.index_id  
                 INNER JOIN sys.columns AS c  
                       ON i.object_id = c.object_id  
                          AND ic.column_id = c.column_id  
          WHERE is_included_column = 0),  
T_CFK AS (SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME,  
                 COLUMN_NAME, ORDINAL_POSITION  
          FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC  
                 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU  
                       ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA  
                          AND TC.TABLE_NAME = KCU.TABLE_NAME  
                          AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME  
          WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY')  
SELECT T_CFK.TABLE_SCHEMA, T_CFK.TABLE_NAME, T_CFK.CONSTRAINT_NAME  
FROM   T_CFK  
EXCEPT      
SELECT T_CFK.TABLE_SCHEMA, T_CFK.TABLE_NAME, T_CFK.CONSTRAINT_NAME  
FROM   T_IDX  
       INNER JOIN T_CFK  
             ON T_IDX.TABLE_SCHEMA = T_CFK.TABLE_SCHEMA   
                AND T_IDX.TABLE_NAME = T_CFK.TABLE_NAME  
                AND T_IDX.COLUMN_NAME = T_CFK.COLUMN_NAME  
WHERE (SELECT COUNT(*)  
       FROM   T_IDX AS X  
       WHERE  X.TABLE_SCHEMA = T_CFK.TABLE_SCHEMA   
         AND  X.TABLE_NAME = T_CFK.TABLE_NAME  
         AND  X.COLUMN_NAME = T_CFK.COLUMN_NAME  
       GROUP  BY INDEX_NAME) = (SELECT COUNT(*)  
                                FROM   T_IDX AS K  
                                WHERE  K.TABLE_SCHEMA = T_CFK.TABLE_SCHEMA   
                                  AND  K.TABLE_NAME = T_CFK.TABLE_NAME  
                                  AND  K.COLUMN_NAME = T_CFK.COLUMN_NAME  
                                 GROUP  BY INDEX_NAME);