【SQL Server】查询数据表主键及主键类型

墨蓝 2023-10-02 12:03 25阅读 0赞

#### 文章目录 ####

*  目的
 *  相关资料
 *   *  获取数据表字段信息（sys.columns）
     *  获取数据类型信息（sys.types）
     *  获取索引信息（sys.indexes）
     *  获取索引字段信息（sys.index\_columns）
 *  示例

## 目的 ##

在做SQL开发的过程中，可能需要获取所有表的主键信息，此时就需要用到SQL Server中的系统表进行多表联查，获取数据表的相关信息。

## 相关资料 ##

### 获取数据表字段信息（sys.columns） ###

首先可以利用系统表中的syscolumns表获取某一数据表的字段信息。示例：

SELECT object_id, name, column_id, system_type_id, max_length, precision, scale, is_nullable FROM sys.columns

该示例提供sys.columns表中较为常用的几列，其中：

<table> 
 <thead> 
  <tr> 
   <th align="left">列名</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">object_id</td> 
   <td align="left">字段所属数据表的ID</td> 
  </tr> 
  <tr> 
   <td align="left">name</td> 
   <td align="left">字段名称</td> 
  </tr> 
  <tr> 
   <td align="left">column_id</td> 
   <td align="left">字段在数据表中的位置</td> 
  </tr> 
  <tr> 
   <td align="left">system_type_id</td> 
   <td align="left">字段类型ID</td> 
  </tr> 
  <tr> 
   <td align="left">max_length</td> 
   <td align="left">字段设置的最大长度（字符型）</td> 
  </tr> 
  <tr> 
   <td align="left">precision</td> 
   <td align="left">字段的长度（数值型）</td> 
  </tr> 
  <tr> 
   <td align="left">scale</td> 
   <td align="left">字段的精度（数值型）</td> 
  </tr> 
  <tr> 
   <td align="left">is_nullable</td> 
   <td align="left">字段是否允许为空</td> 
  </tr> 
 </tbody> 
</table>

### 获取数据类型信息（sys.types） ###

在sys.columns中的字段类型是以Id作为标识，所以不能直观的看出该字段的类型所以可以根据systypes表获取该字段的类型名称。示例：

SELECT name, system_type_id, max_length, precision, scale FROM sys.types

其中挑选了常用的两个字段：

<table> 
 <thead> 
  <tr> 
   <th align="left">字段</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">name</td> 
   <td align="left">字段描述</td> 
  </tr> 
  <tr> 
   <td align="left">system_type_id</td> 
   <td align="left">字段标识Id</td> 
  </tr> 
  <tr> 
   <td align="left">max_length</td> 
   <td align="left">字段最大长度（字符型）</td> 
  </tr> 
  <tr> 
   <td align="left">precision</td> 
   <td align="left">字段长度（数值型）</td> 
  </tr> 
  <tr> 
   <td align="left">scale</td> 
   <td align="left">字段精度（数值型）</td> 
  </tr> 
 </tbody> 
</table>

### 获取索引信息（sys.indexes） ###

通过系统表sys.indexes获取数据表的索引信息。示例：

SELECT object_id, name, index_id, type, type_desc, is_unique, is_primary_key FROM sys.indexes

<table> 
 <thead> 
  <tr> 
   <th align="left">字段</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">object_id</td> 
   <td align="left">索引所属的数据表ID</td> 
  </tr> 
  <tr> 
   <td align="left">name</td> 
   <td align="left">索引名称</td> 
  </tr> 
  <tr> 
   <td align="left">index_id</td> 
   <td align="left">索引ID</td> 
  </tr> 
  <tr> 
   <td align="left">type</td> 
   <td align="left">索引类型<br>1-聚集<br>2-非聚集<br>3-XML<br>4-空间<br>5-聚集列存储索引。 适用于：SQL Server 2014 (12.x) 及更高版本。<br>6-非群集列存储索引。 适用于：SQL Server 2012 (11.x) 及更高版本。<br>7-非群集哈希索引。 适用于：SQL Server 2014 (12.x) 及更高版本。</td> 
  </tr> 
  <tr> 
   <td align="left">type_desc</td> 
   <td align="left">索引描述<br>HEAP-堆<br>CLUSTERED-聚集<br>NONCLUSTERED-非聚集<br>XML-XML<br>SPATIAL-空间<br>CLUSTERED COLUMNSTORE-聚集列存储索引。<br>NONCLUSTERED COLUMNSTORE -非群集列存储索引。<br>NONCLUSTERED HASH：NONCLUSTERED HASH-非群集哈希索引。</td> 
  </tr> 
  <tr> 
   <td align="left">is_unique</td> 
   <td align="left">是否为聚集索引 0 - 非聚集索引 1 - 聚集索引</td> 
  </tr> 
  <tr> 
   <td align="left">is_primary_key</td> 
   <td align="left">是否为主键索引</td> 
  </tr> 
 </tbody> 
</table>

### 获取索引字段信息（sys.index\_columns） ###

在sys.indexes表中只能查找到索引的名称和信息，但是对于索引中包含的字段不能够直接读出。因此可以与sys.index\_columns联查寻找索引包含的字段。示例：

SELECT object_id, index_id, column_id FROM sys.index_columns

<table> 
 <thead> 
  <tr> 
   <th align="left">字段</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">object_id</td> 
   <td align="left">索引字段所属数据表的ID</td> 
  </tr> 
  <tr> 
   <td align="left">index_id</td> 
   <td align="left">索引ID</td> 
  </tr> 
  <tr> 
   <td align="left">column_id</td> 
   <td align="left">数据表字段的ID</td> 
  </tr> 
 </tbody> 
</table>

## 示例 ##

通过以上三个表的联查，可以获取某一数据表的包含字段信息的索引。示例：

-- 获取索引及索引字段信息
    SELECT idx.name, idx.type_desc, cols.name, types.name, cols.max_length, cols.precision, cols.scale, cols.is_nullable
    FROM sys.index_columns idxcols
    INNER JOIN sys.indexes idx ON idx.object_id = idxcols.object_id AND idx.index_id = idxcols.index_id
    INNER JOIN sys.columns cols ON idx.object_id = cols.object_id AND idxcols.column_id = cols.column_id
    INNER JOIN sys.types types ON cols.system_type_id = types.system_type_id
    WHERE idxcols.object_id = object_id('table_name')
    ORDER BY idx.index_id

查询数据表的表结构

BEGIN
    	DECLARE @tableName varchar(128) = 'table_name'
    
    	;WITH primaryKeys AS (
    		SELECT
    			cols.name [primaryKey],
    			'PK' [name]
    		FROM
    			sys.index_columns indexCols
    		INNER JOIN sys.columns cols ON indexCols.object_id = cols.object_id AND indexCols.column_id = cols.column_id
    		INNER JOIN sys.indexes inds ON indexCols.object_id = inds.object_id AND indexCols.index_id = inds.index_id
    		WHERE indexCols.object_id = OBJECT_ID(@tableName, 'u') AND inds.is_primary_key = 1
    	)
    
    	SELECT
    		cols.name [字段],
    		types.name [数据类型],
    		(CASE WHEN cols.max_length = types.max_length THEN ''
    		      WHEN cols.max_length = '-1' THEN 'max'
    		      WHEN cols.system_type_id = 108 THEN '(' + convert(VARCHAR, cols.precision) + ',' + convert(VARCHAR, cols.scale) + ')'
    		      ELSE CONVERT(varchar, cols.max_length) END) [长度],
    		(CASE WHEN cols.is_nullable = 0 THEN 'Y' ELSE '' END) [非空],
    		ISNULL(pk.name, '') [主键],
    		'' [描述]
    	FROM
    		sys.columns cols
    	INNER JOIN sys.types types ON cols.system_type_id = types.system_type_id
    	LEFT JOIN primaryKeys pk ON pk.primaryKey = cols.name
    	WHERE
    		cols.object_id = OBJECT_ID(@tableName, 'U')
    	ORDER BY cols.column_id
    
    END