在數(shù)據(jù)庫內(nèi)創(chuàng)建的每個(gè)對(duì)象（約束、默認(rèn)值、日志、規(guī)則、存儲(chǔ)過程等）在表中占一行。只有在 tempdb 內(nèi)，每個(gè)臨時(shí)對(duì)象才在該表中占一行。
　　sysobjects 表結(jié)構(gòu)：

根據(jù)sysobjects 表格我們可以得到如下的查詢:

1.獲取所有表結(jié)構(gòu)

select name as [表名] from sysobjects where xtype='U'and name !='dtproperties' 
1

2.獲取所有的列

SELECT  d.name                                        表名,       a.name                                           字段名,       ( CASE           WHEN Columnproperty(a.id, a.name, 'IsIdentity') = 1 THEN '是'           ELSE '否'         END )                                          標(biāo)識(shí),          ( CASE           WHEN Columnproperty(a.id, a.name, 'IsIdentity') = 1 THEN  IDENT_Seed( d.name )           ELSE 0         END )                                       標(biāo)識(shí)種子 ,          ( CASE           WHEN Columnproperty(a.id, a.name, 'IsIdentity') = 1 THEN  Ident_Incr(d.name)           ELSE 0         END )                                     標(biāo)識(shí)增長量,       ( CASE           WHEN (SELECT Count(*)                 FROM   sysobjects                 WHERE  ( name IN (SELECT name                                   FROM   sysindexes                                   WHERE  ( id = a.id )                                          AND ( indid IN (SELECT indid                                                          FROM   sysindexkeys                                                          WHERE  ( id = a.id )                                                                 AND ( colid IN (SELECT colid                                                                                 FROM   syscolumns                                                                                 WHERE  ( id = a.id )                                                                                        AND ( name = a.name )) )) )) )                        AND ( xtype = 'PK' )) > 0 THEN '是'           ELSE '否'         END )                                          主鍵,       b.name                                           類型,       a.length                                         占用字節(jié)數(shù),       Columnproperty(a.id, a.name, 'PRECISION')        AS 長度,       Isnull(Columnproperty(a.id, a.name, 'Scale'), 0) AS 小數(shù)位數(shù),       ( CASE           WHEN a.isnullable = 1 THEN '是'           ELSE '否'         END )                                          允許空,       Isnull(e.text, '')                               默認(rèn)值,       Isnull(g.[value], ' ')                           AS [說明]FROM   syscolumns a       LEFT JOIN systypes b              ON a.xtype = b.xusertype       left JOIN sysobjects d               ON a.id = d.id                  AND d.xtype = 'U'                  AND d.name <> 'dtproperties'       LEFT JOIN syscomments e              ON a.cdefault = e.id       LEFT JOIN sys.extended_properties g              ON a.id = g.major_id                 AND a.colid = g.minor_id       LEFT JOIN sys.extended_properties f              ON d.id = f.class                 AND f.minor_id = 0WHERE  b.name IS NOT NULL and d.name is not null--and  d.name='{0}' --如果只查詢指定表,加上此條件ORDER  BY a.id,          a.colorder
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

3.獲取所有的視圖

select b.name as [視圖名稱],a.text as [視圖腳本] from syscomments a inner join sysobjects b on a.id=b.id  where b.type='V'
1

4.獲取所有主鍵約束

 SELECT      tab.name AS [表名],       idxCol.is_descending_key as [是否降序],      idx.name AS [約束名稱],      idx.type_desc as [約束類型],      col.name AS [約束列名]      FROM      sys.indexes idx      JOIN sys.index_columns idxCol      ON (idx.object_id = idxCol.object_id      AND idx.index_id = idxCol.index_id      AND idx.is_primary_key = 1)      JOIN sys.tables tab      ON (idx.object_id = tab.object_id)      JOIN sys.columns col      ON (idx.object_id = col.object_id      AND idxCol.column_id = col.column_id);
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

5.獲取所有唯一約束

SELECT  tab.name AS [表名],  idxCol.is_descending_key as [是否降序],  idx.name AS [約束名稱],  idx.type_desc as [約束類型],  col.name AS [約束列名]FROM  sys.indexes idx    JOIN sys.index_columns idxCol       ON (idx.object_id = idxCol.object_id           AND idx.index_id = idxCol.index_id           AND idx.is_unique_constraint = 1)    JOIN sys.tables tab      ON (idx.object_id = tab.object_id)    JOIN sys.columns col      ON (idx.object_id = col.object_id          AND idxCol.column_id = col.column_id);
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

6.獲取所有外鍵約束

select  oSub.name  AS  [子表名稱],  fk.name AS  [外鍵名稱],  SubCol.name AS [子表列名],  oMain.name  AS  [主表名稱],  MainCol.name AS [主表列名]from  sys.foreign_keys fk      JOIN sys.all_objects oSub          ON (fk.parent_object_id = oSub.object_id)    JOIN sys.all_objects oMain         ON (fk.referenced_object_id = oMain.object_id)    JOIN sys.foreign_key_columns fkCols         ON (fk.object_id = fkCols.constraint_object_id)    JOIN sys.columns SubCol         ON (oSub.object_id = SubCol.object_id              AND fkCols.parent_column_id = SubCol.column_id)    JOIN sys.columns MainCol         ON (oMain.object_id = MainCol.object_id              AND fkCols.referenced_column_id = MainCol.column_id)
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

7.獲取所有Check約束

SELECT  tab.name AS [表名],  chk.name AS [約束名稱],  col.name AS [約束列名],  chk.definition AS [約束定義]FROM  sys.check_constraints chk    JOIN sys.tables tab      ON (chk.parent_object_id = tab.object_id)    JOIN sys.columns col      ON (chk.parent_object_id = col.object_id          AND chk.parent_column_id = col.column_id)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

8.獲取所有默認(rèn)約束

SELECT  tab.name AS [表名],  def.name AS [約束名稱],  col.name AS [約束列名],  def.definition AS [約束定義]      FROMsys.default_constraints def    JOIN sys.tables tab      ON (def.parent_object_id = tab.object_id)    JOIN sys.columns col      ON (def.parent_object_id = col.object_id          AND def.parent_column_id = col.column_id)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

9.獲取所有索引約束

SELECT  tab.name AS [表名],   idx.is_unique  as [是否唯一索引],   idxCol.is_descending_key as [是否降序],  idx.name AS [約束名稱],  idx.type_desc as [約束類型],  col.name AS [約束列名]FROM  sys.indexes idx    JOIN sys.index_columns idxCol       ON (idx.object_id = idxCol.object_id           AND idx.index_id = idxCol.index_id         AND idx.is_unique_constraint= 0 and is_primary_key=0)    JOIN sys.tables tab      ON (idx.object_id = tab.object_id)    JOIN sys.columns col      ON (idx.object_id = col.object_id          AND idxCol.column_id = col.column_id);