菜单

查询数据库的表,一个表中所有字段的名称

2020年5月8日 - 数据网络

小说介绍了有关SQLSE景逸SUVVE奇骏如何获取叁个数据库中的全部表的名称、叁个表中全体字段的名称
,有亟待驾驭的校友可参谋一下。

–自增方面包车型地铁數據

1、查询数据库中的表,个中,XTYPE为U是数额库表,是P为存款和储蓄进程,V为视图等。

1.查询数据库中的全部数据库名:

Select so.name Table_name, –表名字
sc.name Iden_Column_name, –自增字段名字
ident_current(so.name) curr_value, –自增字段当前值
ident_incr(so.name) incr_value, –自增字段拉长值
ident_seed(so.name) seed_value –自增字段种子值
from sysobjects so
Inner Join syscolumns sc
on so.id = sc.id
and columnproperty(sc.id, sc.name, ‘IsIdentity’) = 1
Where upper(so.name) = upper(表名)

SELECT * FROM SysObjects Where XType=’U’ AND name LIKE ‘Task%’ ORDER BY
Name;

代码如下复制代码 SELECT Name FROM Master..SysDatabases OENVISIONDE卡宴 BY Name2.

=====================MS SQL 2000============================

 

询问有些数据库中具备的表名:

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 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
–where d.name=’TWebInfo’ –就算只询问钦赐表,加上此条件
order by a.id,a.colorder

2、查询数据表的列

代码如下复制代码 SELECT Name FROM SysObjects Where XType=’U’ O奇骏DER BY
Name3.

 

SELECT a.name,b.name 

查询表布局消息

=====================MS SQL 2005============================ 

FROM SysObjects a

代码如下复制代码

SELECT  (case when a.colorder=1 then d.name else ” 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 占用字节数,
        COLUMNPROPERTY(a.id,a.name,’PRECISION’) as 长度,
        isnull(COLUMNPROPERTY(a.id,a.name,’Scale’卡塔尔(قطر‎,0State of Qatar as 小数位数,
        (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.major_id –贰零零柒相应的改过
—where d.name=’要查询的表’
order by a.id,a.colorder

LEFT OUTER JOIN syscolumns b ON a.id=b.id

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 ” endState of Qatar 标志, (case when (SELECT
count(*卡塔尔 FROM sysobjects WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.idState of Qatar 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卡塔尔卡塔尔卡塔尔(قطر‎State of Qatar卡塔尔国卡塔尔国State of Qatar AND (xtype = ‘PK’卡塔尔State of Qatar0 then ” else ”
end卡塔尔(قطر‎ 主键,b.name 类型,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,’PRECISION’卡塔尔国 as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,’Scale’卡塔尔(قطر‎,0State of Qatar as 小数位数,(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_idleft join sys.extended_properties f on
d.id=f.class and f.minor_id=0where b.name is not null–WHEREd.name=’要询问的表’ –若是只询问钦赐表,加上此规范order by
a.id,a.colorder

Where a.XType=’U’ AND a.name = ‘TaskLine’

 

3、查询表构造音信

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

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

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图