目录
表名、字段名、字段类型、字段精度、字段大小
字段名、是否为主键、字段类型、字段大小、索引名
表名、字段名、字段类型、字段精度、字段大小
SQL:
案例:
字段名、是否为主键、字段类型、字段大小、索引名
SQL:
use [test] -- 修改库名 SELECT ColumnName=C.name, PrimaryKey=ISNULL(IDX.PrimaryKey,N''), Type=T.name, Length=C.max_length, IndexName=ISNULL(IDX.IndexName,N'') FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id LEFT JOIN -- 索引及主键信息 ( SELECT IDXC.[object_id], IDXC.column_id, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id ) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id WHERE O.name=N'UTIL_IP' -- 修改表名 ORDER BY O.name,C.column_id
案例: