SqlServer如何查詢表的列數(shù)

select count(name) from syscolumns

創(chuàng)新互聯(lián)公司主營魚峰網(wǎng)站建設的網(wǎng)絡公司,主營網(wǎng)站建設方案,重慶APP軟件開發(fā),魚峰h5微信小程序開發(fā)搭建,魚峰網(wǎng)站營銷推廣歡迎魚峰等地區(qū)企業(yè)咨詢

where id=( select id from sysobjects where name='表名' and xtype='U')

說明：select id from sysobjects where name='表名' and xtype='U' 從sysobjects 里查詢表類型為U(非系統(tǒng))的表的id ，假設查到的是 1002 ；

select count(name) from syscolumns where id=1002 查的是系統(tǒng)列syscolumns 里表id是1002的列數(shù)。

你可以隨便建個表，然后分步運行這兩句看看

sqlserver 查詢結果列組成字符串

Select L.lastid,f.firstid,(Select 姓名+':'+電話+',' from [last] where firstid=f.firstid For XML Path(''))

from [first] F left join [last] L

on F.firstid=L.firstid Group by L.lastid,f.firstid

如何在sqlserver中獲取表的所有列信息

1.查詢數(shù)據(jù)庫中的所有數(shù)據(jù)庫名：

SELECT Name FROM Master..SysDatabases ORDER BY Name

2.查詢某個數(shù)據(jù)庫中所有的表名：

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name

3.查詢表結構信息：

SELECT (case when a.colorder=1 then d.name else null end) 表名,

a.colorder 字段序號,a.name 字段名,

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 標識,

(case when (SELECT count(*) FROM sysobjects

WHERE (name in (SELECT name FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))

AND (xtype = 'PK'))0 then '√' else '' end) 主鍵,b.name 類型,a.length 占用字節(jié)數(shù),

COLUMNPROPERTY(a.id,a.name,'PRECISION') as 長度,

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小數(shù)位數(shù),(case when a.isnullable=1 then '√'else '' end) 允許空,

isnull(e.text,'') 默認值,isnull(g.[value], ' ') AS [說明]

FROM syscolumns a

left join systypes b on a.xtype=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 sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id

left join sys.extended_properties f on d.id=f.class and f.minor_id=0

where b.name is not null

--WHERE d.name='要查詢的表' --如果只查詢指定表,加上此條件

order by a.id,a.colorder

SQLSERVER 怎么查詢某一數(shù)值在哪一列 和 哪幾列

就拿你這個來說

select?a+b+c?from?table

得到幾就是幾了唄，如果是其他字符，比如

select?case?when?a='x'?then?1?else?0?end?+case?when?b='x'?then?1?else?0?end?+case?when?c='x'?then?1?else?0?end?from?table

sqlserver 如何根據(jù)條件選擇要檢索的列 case when

SELECT id,

CASE WHEN id=1 THEN firstname ELSE user END 'username'

FROM tb1

如果是查詢條件要用這個就套一個

SELECT *

FROM tb1 a

WHERE username=(SELECT CASE WHEN id=1 THEN firstname ELSE user END

FROM tb1

WHERE id=a.id);