SQL读取表字段信息并写入CMS字段元信息表


SELECT 
[ContentTypeId]= (select top 1 ContentTypeId from spb_cms_ContentTypeDefinitions where TableName= O.name),
[ColumnName]=C.name,
[ColumnLabel]=ISNULL(PFD.[value],N''),
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IsBuiltIn]=1,
[DataType]=case when T.name like '%char' then N'string' 
				when T.name = 'tinyint' then N'bool' 
				when T.name = 'smallint' or T.name = 'int' then N'int'
				when T.name = 'bigint' then N'long' 
				else T.name end,
[Length]=C.max_length,
[Precision]=C.precision,
[IsNotNull]=C.is_nullable,
[DefaultValue]=N'',
[IsIndex]=ISNULL(IDX.IsIndex,0),
[IsUnique]=ISNULL(IDX.IsUnique,0),--todo
[KeyOrIndexName]=ISNULL(IDX.IndexName,N''),
[KeyOrIndexColumns]=case when IDX.IsIndex=1  then C.name else N'' end,
[ControlCode]=N'',
[InitialValue]=N'',
[EnableInput]=1,
[EnableEdit]=1,
[ValidateRole]=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 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 
IDX.object_id,
IDXC.column_id,
IsIndex=1,
IsUnique=IDX.is_unique,
PrimaryKey=IDX.is_primary_key,
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
--WHERE idx.object_id=object_id(N'spb_WikiPages') -- 如果只查询指定表,加上此条件
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id

WHERE O.name=N'spb_WikiPages' -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id

 


评论列表 0

暂无评论