--sql2000 select 表名=object_name(a.depid), 列名=b.name from sysdepends a,syscolumns b where a.depid=b.id and object_name(a.id)='v_test'
--查询表结构信息 SELECT 表名=case when a.colorder=1 then d.name else '' end, 表说明=case when a.colorder=1 then isnull(f.value,'') 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 parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) 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(e.text,''), 字段说明=isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid left join sysproperties f on d.id=f.id and f.smallid=0 inner join sysdepends h on h.depid=a.id where object_name(h.id)='v_test' order by a.id,a.colorder
--简单一点的 select distinct 表名=object_name(a.id), 列名=col_name(object_id(object_name(a.id)),smallid), 描述=value from sysproperties a inner join sysdepends b on b.depid=a.id where object_name(b.id)='v_test'
在2005上通过。 select distinct object_name(a.depid) 表名, b.name 列名, c.name 属性名, c.value 属性值 from sys.sysdepends a inner join syscolumns b on a.depid=b.id inner join sys.extended_properties c on b.colid=c.minor_id where object_name(a.id)='viewtest' -- sys.extended_properties 的class应为1.选取相对应的字段项。
举个例吧 ---table1 column type decription a int columna b int columnb ---table2 a int table2a b varchar ---view1 select table1.a , table1.b,table2.b as tableb from table1,table2 想要查询view的列的结果: a columna b columnb tableb
--简单一点的 select distinct 表名=object_name(a.id), 列名=col_name(object_id(object_name(a.id)),smallid), 描述=value from sysproperties a inner join sysdepends b on b.depid=a.id where object_name(b.id)='v_test' /* 表名 列名 描述 ------------------------------------------------ t_name1 c3 FSDFASD t_name1 id sdfasdfasdfasdfasd t_name2 name abcdefg */这样还不行?晕!!!!
呵呵,不行啊 没有描述信息的字段,没有显示 不信 用我的那个例子测试下 肯定查不tableb那个列
晕 ---table2 a int table2a b varchar tableb根本就没有decription
create table table1(a int,b int) exec sp_addextendedproperty N'MS_Description', N'columna', N'user', N'dbo', N'table', N'table1', N'column', N'a' GO exec sp_addextendedproperty N'MS_Description', N'columnb', N'user', N'dbo', N'table', N'table1', N'column', N'b' GOcreate table table2(a int,b int) exec sp_addextendedproperty N'MS_Description', N'table2a', N'user', N'dbo', N'table', N'table2', N'column', N'a' GO exec sp_addextendedproperty N'MS_Description', N'table2b', N'user', N'dbo', N'table', N'table2', N'column', N'b' GOcreate view v_1 as select table1.a , table1.b,table2.b as tableb from table1,table2 goselect distinct 表名=object_name(a.id), 列名=col_name(object_id(object_name(a.id)),smallid), 描述=value from sysproperties a inner join sysdepends b on b.depid=a.id where object_name(b.id)='v_1'drop table table1,table2 drop view v_1/* 表名 列名 描述 ---------------------------- table1 a columna table1 b columnb table2 a table2a table2 b table2b */
select distinct object_name(a.depid) 表名, b.name 列名, case when c.name isnull then '' else c.name end 属性名, case when c.value isnull then '' else c.value end 属性值 from sys.sysdepends a inner join syscolumns b on a.depid=b.id left outer join sys.extended_properties c on b.colid=c.minor_id where object_name(a.id)='viewtest' --把inner join 改为left outer join 就可以实现了。
case when c.name isnull then '' else c.name end 属性名, 全部显示:MS_Description 所以这个字段估计是取错了
忘加个条件,随后还断网,晕掉 select distinct object_name(a.depid) 表名, b.name 列名, case when c.name is null then '' else c.name end 属性名, case when c.value is null then '' else c.value end 属性值 from sys.sysdepends a inner join syscolumns b on a.depid=b.id left outer join sys.extended_properties c on b.id=c.major_id and b.colorder=c.minor_id where object_name(a.id)='viewtest'
---hrb 取出了view依赖的表的中的所有字段 不论view中是否有该字段 -- 问题还是存在
我也怀疑是否能找到? 多谢 gc_ding 和hrb2008 的热心帮助 揭帖把
看看这样行不? --查询视图中列描述 create table table1(a int,b int) exec sp_addextendedproperty N'MS_Description', N'columna', N'user', N'dbo', N'table', N'table1', N'column', N'a' GO exec sp_addextendedproperty N'MS_Description', N'columnb', N'user', N'dbo', N'table', N'table1', N'column', N'b' GOcreate table table2(c int,d int) exec sp_addextendedproperty N'MS_Description', N'table2b', N'user', N'dbo', N'table', N'table2', N'column', N'd' GO -- exec sp_addextendedproperty N'MS_Description', N'table2b', N'user', N'dbo', N'table', N'table2', N'column', N'd' -- GOcreate view v_1 as select table1.a , table1.b,table2.c from table1,table2 goselect distinct 列名=col_name(object_id(object_name(a.id)),smallid), 描述=a.value into #1 from sysproperties a right join sysdepends b on b.depid=a.id where object_name(b.id)='v_1'select 列名=name into #2 from syscolumns WHERE object_name(id)='v_1'select x.列名,y.描述 from #2 x left join #1 y on x.列名=y.列名 drop table table1,table2 drop view v_1 drop table #1,#2/* 列名 描述 --------------------- a columna b columnb c NULL */
--谢谢楼上的你的sql 可以得出view依赖的所有table的所有列的描述信息
可是我需要知道那些是在view中出现的列
只显示在view中出现的列信息
select
表名=object_name(a.depid),
列名=b.name
from
sysdepends a,syscolumns b
where
a.depid=b.id
and object_name(a.id)='v_test'
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') 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 parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) 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(e.text,''),
字段说明=isnull(g.[value],'')
FROM
syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
inner join sysdepends h on h.depid=a.id
where
object_name(h.id)='v_test'
order by
a.id,a.colorder
我想确定,那些是view中的列
只取出该列信息
sp_help '视图名称'
sp_help '视图名称'---\
查询了
不是我想要的信息
我要是视图中列的描述信息
有个description的对列的说明,我想取得这个说明
table的我已经取得了
就是view中的列,我取不到
select distinct
表名=object_name(a.id),
列名=col_name(object_id(object_name(a.id)),smallid),
描述=value
from
sysproperties a
inner join
sysdepends b on b.depid=a.id
where
object_name(b.id)='v_test'
相对应即可
select distinct
object_name(a.depid) 表名,
b.name 列名,
c.name 属性名,
c.value 属性值
from
sys.sysdepends a
inner join
syscolumns b
on a.depid=b.id
inner join sys.extended_properties c
on b.colid=c.minor_id
where object_name(a.id)='viewtest'
--
sys.extended_properties 的class应为1.选取相对应的字段项。
也要显示出来,为空就好
--hrb2008的字段选择有问题
--gc的没有显示出没有描述信息的字段
---table1
column type decription
a int columna
b int columnb ---table2
a int table2a
b varchar ---view1
select table1.a , table1.b,table2.b as tableb from table1,table2 想要查询view的列的结果:
a columna
b columnb
tableb
select distinct
表名=object_name(a.id),
列名=col_name(object_id(object_name(a.id)),smallid),
描述=value
from
sysproperties a
inner join
sysdepends b on b.depid=a.id
where
object_name(b.id)='v_test'
/*
表名 列名 描述
------------------------------------------------
t_name1 c3 FSDFASD
t_name1 id sdfasdfasdfasdfasd
t_name2 name abcdefg
*/这样还不行?晕!!!!
没有描述信息的字段,没有显示
不信
用我的那个例子测试下
肯定查不tableb那个列
---table2
a int table2a
b varchar tableb根本就没有decription
exec sp_addextendedproperty N'MS_Description', N'columna', N'user', N'dbo', N'table', N'table1', N'column', N'a'
GO
exec sp_addextendedproperty N'MS_Description', N'columnb', N'user', N'dbo', N'table', N'table1', N'column', N'b'
GOcreate table table2(a int,b int)
exec sp_addextendedproperty N'MS_Description', N'table2a', N'user', N'dbo', N'table', N'table2', N'column', N'a'
GO
exec sp_addextendedproperty N'MS_Description', N'table2b', N'user', N'dbo', N'table', N'table2', N'column', N'b'
GOcreate view v_1
as
select table1.a , table1.b,table2.b as tableb from table1,table2
goselect distinct
表名=object_name(a.id),
列名=col_name(object_id(object_name(a.id)),smallid),
描述=value
from
sysproperties a
inner join
sysdepends b on b.depid=a.id
where
object_name(b.id)='v_1'drop table table1,table2
drop view v_1/*
表名 列名 描述
----------------------------
table1 a columna
table1 b columnb
table2 a table2a
table2 b table2b
*/
该字段tableb也要显示,并且是显示视图中变化后的列名没有描述信息,就显示为空,但是要显示字段信息
实际需求为:
取出view中所有字段,有描述信息的显示描述信息,
没有的描述信息显示为空,但字段名要显示出来----
gc你显示的是所有有描述信息的对应table中字段,
而不是 view中的字段名别名,没有表述信息的没有显示
--
呵呵,就是这么麻烦的要求
哎
查了一天sql help了
object_name(a.depid) 表名,
b.name 列名,
case when c.name isnull then '' else c.name end 属性名,
case when c.value isnull then '' else c.value end 属性值
from
sys.sysdepends a
inner join
syscolumns b
on a.depid=b.id
left outer join sys.extended_properties c
on b.colid=c.minor_id
where object_name(a.id)='viewtest'
--把inner join 改为left outer join 就可以实现了。
问题:
把不是视图中的列也取出来了
换句话说
把视图依赖的表中的所有字段都取出来了
并且描述字段显示信息有错误,
有的字段没有描述信息,
也显示了和别的字段一样的描述信息
全部显示:MS_Description
所以这个字段估计是取错了
select distinct
object_name(a.depid) 表名,
b.name 列名,
case when c.name is null then '' else c.name end 属性名,
case when c.value is null then '' else c.value end 属性值
from sys.sysdepends a
inner join syscolumns b
on a.depid=b.id
left outer join
sys.extended_properties c
on b.id=c.major_id
and
b.colorder=c.minor_id
where object_name(a.id)='viewtest'
取出了view依赖的表的中的所有字段
不论view中是否有该字段
--
问题还是存在
多谢
gc_ding
和hrb2008
的热心帮助
揭帖把
--查询视图中列描述
create table table1(a int,b int)
exec sp_addextendedproperty N'MS_Description', N'columna', N'user', N'dbo', N'table', N'table1', N'column', N'a'
GO
exec sp_addextendedproperty N'MS_Description', N'columnb', N'user', N'dbo', N'table', N'table1', N'column', N'b'
GOcreate table table2(c int,d int)
exec sp_addextendedproperty N'MS_Description', N'table2b', N'user', N'dbo', N'table', N'table2', N'column', N'd'
GO
-- exec sp_addextendedproperty N'MS_Description', N'table2b', N'user', N'dbo', N'table', N'table2', N'column', N'd'
-- GOcreate view v_1
as
select table1.a , table1.b,table2.c from table1,table2
goselect distinct
列名=col_name(object_id(object_name(a.id)),smallid),
描述=a.value
into #1
from
sysproperties a
right join
sysdepends b on b.depid=a.id
where
object_name(b.id)='v_1'select
列名=name
into #2
from
syscolumns
WHERE
object_name(id)='v_1'select x.列名,y.描述
from #2 x
left join #1 y on x.列名=y.列名
drop table table1,table2
drop view v_1
drop table #1,#2/*
列名 描述
---------------------
a columna
b columnb
c NULL
*/