SQL2005 返回数据库指定表的字段名、字段类型、字段长代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQL2005 返回数据库指定表的字段名、字段类型、字段长

作者:Dezai.CN / 发布于2011/12/13/ 756

<div>
<span>--SQL2005 返回数据库指定表的字段名、字段类型、字段长度、主键等数据
SELECT 
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END, 
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''), 
Column_id=C.column_id, 
ColumnName=C.name, 
PrimaryKey=ISNULL(IDX.PrimaryKey,N''), 
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'&radic;'ELSE N'' END, 
Computed=CASE WHEN C.is_computed=1 THEN N'&radic;'ELSE N'' END, 
Type=T.name, 
Length=C.max_length, 
Precision=C.precision, 
Scale=C.scale, 
NullAble=CASE WHEN C.is_nullable=1 THEN N'&radic;'ELSE N'' END, 
[Default]=ISNULL(D.definition,N''), 
ColumnDesc=ISNULL(PFD.[value],N''), 
IndexName=ISNULL(IDX.IndexName,N''), 
IndexSort=ISNULL(IDX.Sort,N''), 
Create_Date=O.Create_Date, 
Modify_Date=O.Modify_date 
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 sys.default_constraints D 
ON C.[object_id]=D.parent_object_id 
AND C.column_id=D.parent_column_id 
AND C.default_object_id=D.[object_id] 
LEFT JOIN sys.extended_properties PFD 
ON PFD.class=1 
AND C.[object_id]=PFD.major_id 
AND C.column_id=PFD.minor_id 
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) 
LEFT JOIN sys.extended_properties PTB 
ON PTB.class=1 
AND PTB.minor_id=0 
AND C.[object_id]=PTB.major_id 
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 
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'&radic;'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 
LEFT JOIN sys.key_constraints KC 
ON IDX.[object_id]=KC.[parent_object_id] 
AND IDX.index_id=KC.unique_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'p_sys01_test' -- 如果只查询指定表,加上此条件 </span>
</div>

试试其它关键字

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)