SQLServer很有用的查詢語句

1、索引相關查詢

 1 SELECT 
 2  表名=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END, 
 3  表說明=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''), 
 4  欄位序號=C.column_id, 
 5  欄位名=C.name,
 6  主鍵=ISNULL(IDX.PrimaryKey,N''), 
 7  標識=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END, 
 8  計算列=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, 
 9  類型=T.name, 
10  長度=C.max_length, 
11  精度=C.precision, 
12  小數位數=C.scale, 
13  允許空=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, 
14  預設值=ISNULL(D.definition,N''), 
15  欄位說明=ISNULL(PFD.[value],N''), 
16  索引名=ISNULL(IDX.IndexName,N''), 
17  索引排序=ISNULL(IDX.Sort,N''), 
18  創建時間=O.Create_Date, 
19  修改時間=O.Modify_date 
20 FROM sys.columns C 
21  INNER JOIN sys.objects O 
22      ON C.[object_id]=O.[object_id] 
23          AND O.type='U' 
24          AND O.is_ms_shipped=0 
25  INNER JOIN sys.types T 
26      ON C.user_type_id=T.user_type_id 
27  LEFT JOIN sys.default_constraints D 
28      ON C.[object_id]=D.parent_object_id 
29          AND C.column_id=D.parent_column_id 
30          AND C.default_object_id=D.[object_id] 
31 LEFT JOIN sys.extended_properties PFD 
32      ON PFD.class=1 
33          AND C.[object_id]=PFD.major_id 
34          AND C.column_id=PFD.minor_id 
35 --             AND PFD.name='Caption'  -- 欄位說明對應的描述名稱(一個欄位可以添加多個不同name的描述) 
36  LEFT JOIN sys.extended_properties PTB 
37      ON PTB.class=1 
38          AND PTB.minor_id=0 
39          AND C.[object_id]=PTB.major_id 
40 --             AND PFD.name='Caption'  -- 表說明對應的描述名稱(一個表可以添加多個不同name的描述) 
41  LEFT JOIN                       -- 索引及主鍵信息 
42  ( 
43      SELECT 
44          IDXC.[object_id], 
45          IDXC.column_id, 
46          Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') 
47              WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, 
48          PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, 
49          IndexName=IDX.Name 
50      FROM sys.indexes IDX 
51      INNER JOIN sys.index_columns IDXC 
52 ON IDX.[object_id]=IDXC.[object_id] 
53              AND IDX.index_id=IDXC.index_id 
54      LEFT JOIN sys.key_constraints KC 
55          ON IDX.[object_id]=KC.[parent_object_id] 
56              AND IDX.index_id=KC.unique_index_id 
57      INNER JOIN  -- 對於一個列包含多個索引的情況,只顯示第1個索引信息 
58      ( 
59          SELECT [object_id], Column_id, index_id=MIN(index_id) 
60          FROM sys.index_columns 
61          GROUP BY [object_id], Column_id 
62      ) IDXCUQ 
63          ON IDXC.[object_id]=IDXCUQ.[object_id] 
64              AND IDXC.Column_id=IDXCUQ.Column_id 
65              AND IDXC.index_id=IDXCUQ.index_id 
66  ) IDX 
67      ON C.[object_id]=IDX.[object_id] 
68          AND C.column_id=IDX.column_id 
69 --WHERE O.name = 'KPI_ECSSSnapshot'
70 --如果只查詢指定表,加上此條件 
71 ORDER BY O.name,C.column_id 

2、觸發器相關查詢

查看當前庫中所有的觸發器:

 1 SELECT * FROM Sysobjects WHERE xtype = 'TR' 

查看當前庫中所有的觸發器和與之相對應的表：

1 SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type='TR'