/*********************************************************************************************
Function:查询LandaV8字段属性以及清单详细
Author:Bean
Date:2012-09-24
*********************************************************************************************/
if OBJECT_ID('sp_getTableColInfo','P') is not null drop proc sp_getTableColInfo
go
create proc sp_getTableColInfo
@tablename varchar(128),
@GGUID uniqueidentifier
as
Begin
with cte as
(
select
--字段名称描述
b.desc0 as ColDesc,
--字段名称
a.name as ColName,
--获取字段类型
case a.xusertype
when '167' then 'varchar('+LTRIM(a.length)+')'
when '61' then 'datetime'
when '36' then 'uniqueidentifier'
when '56' then 'int'
when '108' then 'numeric(38,2)'
when '34' then 'image'
else ltrim(a.xusertype) end as [ColType],
--获取清单信息
ListDetails=
(
select CID+':'+CHAR(9)+DESC0+CHAR(10)
from SMLSTD
where ltrim(LHGUID)=ltrim(b.CTRLID)
order by CID
for xml path('')
),
--获取是否允许为空
case B.PNULL when 1 then '√'
when 0 then ''
else ltrim(b.PNULL) end as [Null],
--排序字段
B.Zorder
from syscolumns a
left join SMTMPD b on a.name=b.DID
where
a.id=OBJECT_ID(@tablename)
and b.GGUID=@GGUID
)
select
ColDesc,
ColName,
ColType,
[Null],
isnull(ListDetails,'') as ListDetails
from cte
order by ZORDER
End
go
sp_getTableColInfo
'CSEMPL_1',
'1e3411fa-7c0a-4034-b998-42362cb1d695'
(2期)查询表结构及清单信息
最新推荐文章于 2023-09-18 23:17:12 发布