取列全部用的 sys. 中的表
CTE:WITH name AS() 用法: sql 树形查询 https://www.cnblogs.com/Sea1ee/p/10306800.html
1主键信息
- SELECT ic.column_id,
- ic.index_column_id,
- ic.object_id
- FROM sys.indexes idx
- INNER JOIN sys.index_columns ic
- ON idx.index_id = ic.index_id
- AND idx.object_id = ic.object_id
- WHERE idx.object_id = OBJECT_ID('Course')
- AND idx.is_primary_key = 1;
2改表中的列
- SELECT *
- FROM sys.columns colm
- INNER JOIN sys.types systype
- ON colm.system_type_id = systype.system_type_id
AND systype.user_type_id = colm.user_type_id -- 这两个条件过滤得到用户创建的列
WHERE colm.object_id = OBJECT_ID('Course');
3最终 sql 语句:
- WITH indexCTE
- AS (SELECT ic.column_id,
- ic.index_column_id,
- ic.object_id
- FROM sys.indexes idx
- INNER JOIN sys.index_columns ic
- ON idx.index_id = ic.index_id
- AND idx.object_id = ic.object_id
WHERE idx.object_id = OBJECT_ID('Course') -- 找到该表的主键信息
AND idx.is_primary_key = 1)
SELECT colm.column_id ColumnID, -- 列 id
- CAST(CASE
- WHEN indexCTE.column_id IS NULL THEN
- 0
- ELSE
- 1
- END AS BIT) IsPrimaryKey,
colm.name ColumnName, -- 列名称
systype.name ColumnType, -- 列类型
colm.is_identity IsIdentity, -- 是否自增长
colm.is_nullable IsNullable, -- 是否为空
CAST(colm.max_length AS INT) ByteLength, -- sys.columns 中的 max_length 是字节
- (CASE
- WHEN systype.name = 'nvarchar'
- AND colm.max_length> 0 THEN
- colm.max_length / 2
- WHEN systype.name = 'nchar'
- AND colm.max_length> 0 THEN
- colm.max_length / 2
- WHEN systype.name = 'ntext'
- AND colm.max_length> 0 THEN
- colm.max_length / 2
- ELSE
- colm.max_length
- END
) CharLength, -- 得到字符类型长度
- CAST(colm.precision AS INT) Precision,
- CAST(colm.scale AS INT) Scale,
sep.value Remark -- 列描述
- FROM sys.columns colm
- INNER JOIN sys.types systype
- ON colm.system_type_id = systype.system_type_id
AND systype.user_type_id = colm.user_type_id -- 通过两个关联进行过滤得到用户创建的类型
LEFT JOIN sys.extended_properties sep
ON sep.major_id = colm.object_id -- 得到是这个表的
AND colm.column_id = sep.minor_id -- 这列的
- LEFT JOIN indexCTE
- ON indexCTE.column_id = colm.column_id
- AND indexCTE.object_id = colm.object_id
- WHERE colm.object_id = OBJECT_ID('Course');
来源: http://www.bubuko.com/infodetail-2931783.html