大学IT网 - 最懂大学生的IT学习网站! QQ资料交流群:367606806
当前位置:大学IT网 > MSSQL技巧 > 读取sql server表的字段名,字段类型,字段长度,字段属性描述的sql语句

读取sql server表的字段名,字段类型,字段长度,字段属性描述的sql语句

关键词:sqlserver字段名字段类型字段长度  阅读(1894) 赞(32)

[摘要]本文主要介绍如何读取sql server表的字段名,字段类型,字段长度,字段属性描述的sql语句,与大家分享。

得到表的字段名(排序):

SELECT a.name as 字段名 FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' where d.name = '表名' order by a.id,a.colorder

得到表的字段名:

Select name  from syscolumns Where ID=OBJECT_ID('表名')

可横向获字段名:

select * from 表名

declare @str varchar(4000)

set @str=''

select @str=@str+','+ 字段名 from 表名

select right(@str,len(@str)-1)

print @str

打印可获STR长字符。

另得表的字段名,字段类型,字段长度,字段属性:

SELECT a.name 字段名,COLUMNPROPERTY(a.id,a.name,'IsIdentity') 标识,(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 '1' else '0' end) 主键,b.name 类型,COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '1'else '0' end) 允许空,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 sysproperties g on a.id=g.id AND a.colid = g.smallid

where d.name = 'input'

order by a.id,a.colorder

SQL 查看所有表名:

select name from sysobjects where type='U'

查询表的所有字段名:

Select name from syscolumns Where ID=OBJECT_ID('表名')

select * from information_schema.tables

select * from information_schema.views

select * from information_schema.columns

ACCESS

查看所有表名:

select name from MSysObjects where type=1 and flags=0

MSysObjects是系统对象,默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。

SQL查询表的备注说明

SELECT 表名 = case when a.colorder = 1 then d.name

                  else '' end,

      表说明 = case when a.colorder = 1 then isnull(f.value, '')

                    else '' end

FROM syscolumns a

      inner join sysobjects d

         on a.id = d.id

            and d.xtype = 'U'

            and d.name <> 'sys.extended_properties'

      left join sys.extended_properties   f

        on a.id = f.major_id

           and f.minor_id = 0

SQL查询表的所有字段的备注说明

SQL_1:

SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,

systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,

sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as

COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns

INNER JOIN systypes

   ON syscolumns.xtype = systypes.xtype

   LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id

 LEFT OUTER JOIN sys.extended_properties ON

 ( sys.extended_properties.minor_id = syscolumns.colid

   AND sys.extended_properties.major_id = syscolumns.id)

 LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id

 WHERE syscolumns.id IN

   (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')

   ORDER BY syscolumns.colid

SQL_2:

SELECT

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

a.colorder N'字段序号',

a.name N'字段名',

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

end) N'标识',

(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) N'主键',

b.name N'类型',

a.length N'占用字节数',

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

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',

(case when a.isnullable=1 then '√'else '' end) N'允许空',

isnull(e.text,'') N'默认值',

isnull(g.[value],'') AS N'字段说明'

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

order by object_name(a.id),a.colorder



相关评论