查找某个表或视图的所有字段

– 查找某个表或视图的所有字段最终语句，xhxtest为要查找的表名或视图名，U表示查找表，如果查找视图把U改为V

– 如果想要用其他符号分割，就将,替换为其他符号

select fieldName = STUFF((select ','+B.name from  SYSOBJECTS A ,SYSCOLUMNS B where A.xtype='U' and A.id = B.id and A.NAME='xhxtest'  ORDER BY b.colid for xml path('')),1,1,'')

– 以下为分析过程

– SYSOBJECTS 表存着所有表名

select * from  SYSOBJECTS where xtype='U'

– SYSCOLUMNS 表存着所有列名，SYSOBJECTS和SYSCOLUMNS通过id关联

select * from  SYSCOLUMNS

– 获取表和表对应的字段，通过id相同

select A.name,B.name,b.colid from  SYSOBJECTS A ,SYSCOLUMNS B where A.xtype='U' and A.id = B.id and A.NAME='xhxtest'  ORDER BY b.colid

– 获取表的所有字段，用逗号拼接,用到了for xml path函数

select ','+B.name from  SYSOBJECTS A ,SYSCOLUMNS B where A.xtype='U' and A.id = B.id and A.NAME='xhxtest'  ORDER BY b.colid for xml path('')

– 上面的结果最前面会多个逗号，去掉逗号，用STUFF函数

select STUFF((select ','+B.name from  SYSOBJECTS A ,SYSCOLUMNS B where A.xtype='U' and A.id = B.id and A.NAME='xhxtest'  ORDER BY b.colid for xml path('')),1,1,'')

– 可以这么写查询

select name = 'xhx'select 'xhx'

– 可以对刚刚的结果再加个字段名fieldName

select fieldName = STUFF((select ','+B.name from  SYSOBJECTS A ,SYSCOLUMNS B where A.xtype='U' and A.id = B.id and A.NAME='xhxtest'  ORDER BY b.colid for xml path('')),1,1,'')

– 如果查询视图就把U改为V

select fieldName = STUFF((select ','+B.name from  SYSOBJECTS A ,SYSCOLUMNS B where A.xtype='V' and A.id = B.id and A.NAME='test3'  ORDER BY b.colid for xml path('')),1,1,'')

– stuff 函数用法删除指定长度的字符，并在指定的起点处插入另一组字符。

– character_expression为原始和要替换的参数，start为从哪儿位置开始删除，length为删除的长度

STUFF ( character_expression , start , length ,character_expression )