if exists (select 1
from  sysobjects
where  id = object_id('TableDesc')
and  type = 'FN')
drop function dbo.TableDesc
go
create function TableDesc(@TableName varchar(30),@RowID int)
returns varchar(100)
as
begin
declare @value varchar(100)
select @value=convert(varchar(100),( select distinct value ded_properties g,sysobjects d
where d.id=g.major_id and d.name=@TableName and g.minor_id =0))
declare @strTableName varchar(100),@strTableDesc varchar(100);
with s1 as
(select row_number() over(order by ID)as num,* from dbo.Split(@value,'--'))
SELECT @strTableName=(select ID FROM s1 where num=1);
with s2 as
(select row_number() over(order by ID)as num,* from dbo.Split(@value,'--'))
SELECT @strTableDesc=(select ID FROM s2 where num=2);
if(len(@strTableDesc)>len(@strTableName))
begin
if(@RowID=1)
select @value=@strTableName
else
select @value=@strTableDesc
if(@RowID=2)
select @value=@strTableDesc
else
select @value=@strTableName
end
else
begin
if(@RowID=2)
select @value=@strTableName
else
select @value=@strTableDesc
if(@RowID=1)
select @value=@strTableDesc
select distinct fromelse
select @value=@strTableName
end
return @value
end
go
SELECT 表名=case  when  a.colorder=1  then  d.name 
lorder=2 then dbo.TableDesc(d.name,1)
else  ''  end, 
字段序号=a.colorder, 
字段名=a.name, 
标识=case  when  COLUMNPROPERTY(  a.id,a.name,'IsIdentity')=1  then  '√'else  ''  end, 
主键=case  when  exists(SELECT  1  FROM  sysobjects  where  xtype='PK'  and  name  in  ( 
SELECT  name  FROM  sysindexes  WHERE  indid  in( 
SELECT  indid  FROM  sysindexkeys  WHERE  id  =  a.id  AND  lid 
)))  then  '√'  else  ''  end, 
类型=b.name, 
占用字节=a.length, 
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 
允许空=case  when  a.isnullable=1  then  '√'else  ''  end, 
默认值=,''), 
字段说明=isnull(g.value,''),
表说明=case lorder=1 then dbo.TableDesc(d.name,2) else '' end
FROM syscolumns a 
left join systypes b pe=b.xusertype 
inner join sysobjects d on a.id=d.id pe='U'
left join syscomments e on a.cdefault=e.id 
left ded_properties g  on a.id=g.major_id lid=g.minor_id
order by d.lorder
--当查询完成后将函数删除
if exists (select 1
from  sysobjects
where  id = object_id('TableDesc')
and  type = 'FN')
drop function dbo.TableDesc
go

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。