列出某张表中所有字段名称和字段类型(sqlserver)

select * from sanzhou.dbo.Customerinfo where 1=2 --快速列出表Customerinfo所有字段名称,并且忽略大小写,即:dbo.customerinfo也可以

select name from syscolumns where id=object_id(N’sanzhou.dbo.CustomerInfo’) – 列出所有字段名称,纵向排列,正规写法(加了N)
select name from syscolumns where id=object_id(‘sanzhou.dbo.CustomerInfo’) --同上,非正规写法

select count(name) from syscolumns where id=object_id(‘sanzhou.dbo.CustomerInfo’) --列出表CustomerInfo中的字段个数,正规写法(加了N)
select count(name) from syscolumns where id=object_id(N’sanzhou.dbo.CustomerInfo’) --同上

select name from sysobjects where xtype=‘U’ --查询选定的数据库中所有的表名,无序
select name from sysobjects where xtype=‘U’ order by name --查询选定的数据库中所有的表名,按照英文字母排序

–列出表CustomerInfo中所有字段和字段类型:
select COLUMN_NAME , DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where table_name = ‘CustomerInfo’ --表名

select COLUMN_NAME 列名, DATA_TYPE 字段类型
from INFORMATION_SCHEMA.COLUMNS
where table_name = ‘CustomerInfo’ --表名