XTYPE为U是数据库表新葡京32450网址

发布时间:2020-05-15  栏目:数据  评论:0 Comments

查询表结构信息

–自增方面的數據

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

ORDER BY 表名

查询表字段、注释和类型

–查询表字段、注释、类型
select B.name,C.value,D.name from sysobjects AS A inner join syscolumns
as B
on A.id=B.id inner join sysproperties as c on B.id=C.id and
B.colid=C.smallid inner join systypes D on B.xtype = D.xtype

代码如下复制代码 SELECT Name FROM Master..SysDatabases ORDER BY Name2.

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

3、查询表结构信息

=============================================
获取MS SQL库数据字典的经典SQL语句

文章介绍了关于SQLSERVER如何获取一个数据库中的所有表的名称、一个表中所有字段的名称
,有需要了解的同学可参考一下。

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(表名)

 

order   by   a.id,a.colorder

用SQL查询分析器查询表的字段类型长度和表说明

SELECT sysobjects.name AS 表名, syscolumns.name AS 列名,
systypes.name AS 数据类型, syscolumns.length AS 数据长度,
CONVERT(char,
sysproperties.[value]) AS 注释
FROM sysproperties RIGHT OUTER JOIN
sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON
sysproperties.id = syscolumns.id AND
sysproperties.smallid = syscolumns.colid
WHERE (sysobjects.xtype = ‘u’ OR
sysobjects.xtype = ‘v’) AND (systypes.name <> ‘sysname’)
–and CONVERT(char,sysproperties.[value]) <> ‘null’
–导出注释不为’null’的记录
–AND (sysobjects.name = ‘bbs_bank_log’)
–逐个关联表名,可以用or连接条件

1.查询数据库中的所有数据库名:

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

 AND
(xtype = ‘PK’))>0 then ‘√’ else ” end) 主键,b.name 类型,a.length
占用字节数,  

exec TestPro ‘b’

根据syscolumns得到id所对应的表

查看字段所属表
select * from dbo.sysobjects where id in

查询某个数据库中所有的表名:

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

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

where a.name=’branchinfo’

SQL单引号的转义

create proc TestPro
(@conditon varchar(50))
as
declare @sql varchar(1000)
set @sql=’select * from test’
if @conditon!=”
set @sql=@sql+’ where name=”’+@conditon+””–”转义成’
exec(@sql)
go
–调用存储过程

代码如下复制代码 SELECT Name FROM SysObjects Where XType=’U’ ORDER BY
Name3.

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’),0) 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 –2005相应的修改
on a.id=g.major_id AND a.colid = g.major_id –2005相应的修改
—where d.name=’要查询的表’
order by a.id,a.colorder

 (SELECT
indid FROM sysindexkeys  

–得到数据库中所有用户表
Select [name] from sysObjects Where xtype=’U’and
[name]<>’dtproperties’ Order By [name]
–得到数据库中所有用户视图
Select [name] From sysObjects Where xtype=’V’ And
[name]<>’syssegments’ And [name]<>’sysconstraints’ Order
By [name]
–获得指定表中所有的列
Select
c.name As ColumnName,
t.name As TypeName
From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype
And c.id = o.id
And o.name = ‘Book’
Order By c.colorder
–获得表中所有列的详细信息
Select  ColOrder = col.colorder, –排序号
ColumnName = col.name, –列名
TypeName = type.name,–数据类型名称
Length =  (Case When type.name=’nvarchar’ Or type.name=’nchar’ Then
col.length/2 Else col.length End), –长度
[PRECISION] = COLUMNPROPERTY(col.id, col.name, ‘PRECISION’), –精度
Scale = ISNULL(COLUMNPROPERTY(col.id, col.name, ‘Scale’), 0), –小数
IsIdentity = Case When COLUMNPROPERTY(col.id, col.name, ‘IsIdentity’)=1
Then ‘√’ Else ” End, –是否为自动编号列
IsPK = 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 = col.id And colid =
col.colid
      )
     )
    ) Then ‘√’ Else ” End, –是否为主键
AllowNull = Case When col.isnullable=1 Then ‘√’ Else ” End,
–是否允许为空
DefalutValue = isnull(com.text, ”) –默认值
From syscolumns col
Left Join systypes type On col.xtype = type.xusertype
Inner Join sysobjects obj On col.id = obj.id And (obj.xtype = ‘U’ Or
obj.xtype = ‘V’) And obj.name <> ‘dtproperties’
Left Join syscomments com On col.cdefault = com.id
Where obj.name = ‘Territories’

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 占用字节数,
COLUMNPROPERTY(a.id,a.name,’PRECISION’) as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) 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–WHERE
d.name=’要查询的表’ –如果只查询指定表,加上此条件order by
a.id,a.colorder

 

 isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0)
as 小数位数,(case when a.isnullable=1 then ‘√’else ” end)
允许空,  

WHERE (sysobjects.xtype = ‘U’)

获取数据库中表的字段的名称及类型

select   syscolumns.name,systypes.name       from      
syscolumns,systypes     where       id=object_id( ‘POSmanage..PayWays
‘)   and   systypes.xusertype=syscolumns.xusertype
给你一个通过查询系统表得到纵向的表结构的例子.完全可以满足你的要求.
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= ‘shebei ‘

代码如下复制代码

 inner
join sysobjects d on a.id=d.id and d.xtype=’U’ and
d.name<>’dtproperties’ 

(select id from dbo.syscolumns where name=’列名’)

一条语句查询数据库中所有表的信息

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.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
left join sysproperties f on d.id=f.id and f.smallid=0
–where d.name=’此处可添加要查询的表名’
Order by a.id,a.colorder
可以在where语句中填写要查询指定表的名称,将显示指定的结构信息和说明。
sqlserver系统表结构说明

该说明在帮助文档中应该也是能查询到的
sysaltfiles  主数据库 保存数据库的文件 syscharsets 
主数据库字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库当前配置选项
  sysdatabases 主数据库服务器中的数据库
  syslanguages 主数据库语言
  syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
  sysprocesses 主数据库进程
  sysremotelogins主数据库 远程登录帐号
  syscolumns 每个数据库 列
  sysconstrains 每个数据库 限制
  sysfilegroups 每个数据库 文件组
  sysfiles 每个数据库 文件
  sysforeignkeys 每个数据库 外部关键字
  sysindexs 每个数据库 索引
  sysmenbers 每个数据库角色成员
  sysobjects 每个数据库所有数据库对象
  syspermissions 每个数据库 权限
  systypes 每个数据库 用户定义数据类型
  sysusers 每个数据库 用户

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

SELECT sysobjects.name AS [table], sysproperties.[value] AS
表说明,
syscolumns.name AS field, properties.[value] AS 字段说明,
systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id,
syscolumns.name,
‘Scale’), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN ” ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, ‘IsIdentity’)
= 1 THEN ‘√’ ELSE ” END AS 标识, 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 = syscolumns.id AND colid = syscolumns.colid)))
THEN ‘√’ ELSE ” END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id

留下评论

网站地图xml地图