查询问题

Question

如何使用一条说语句查询数据库,
表名,字段,数据类型,字节,是否为空`高手帮帮忙`

Answer 1

--查所有列信息
select * from syscolumns--查所有用户表信息
select * from sysobjects where type='U'

Answer 2

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 parent_obj=a.id 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='要查询的表'    --如果只查询指定表,加上此条件
order by 
    a.id,a.colorder

Answer 3

如何使用一条说语句查询数据库, 
表名,字段,数据类型,字节,是否为空` 
sp_help jobs
/*Name                                                                                                                             Owner                                                                                                            jobs                                                                                                                             dbo                                                                                                                              user table                      2000-08-06 01:33:52.983 
 
Column_name                                                                                                                      Type                                                                                                                             Computed                            Length      Prec  Scale Nullable                            TrimTrailingBlanks                  FixedLenNullInSource                Collation                                                                                       
job_id                                                                                                                           smallint                                                                                                                         no                                  2           5     0     no                                  (n/a)                               (n/a)                               NULL
job_desc                                                                                                                         varchar                                                                                                                          no                                  50                      no                                  no                                  no                                  Chinese_PRC_CI_AS
min_lvl                                                                                                                          tinyint                                                                                                                          no                                  1           3     0     no                                  (n/a)                               (n/a)                               NULL
max_lvl                                                                                                                          tinyint                                                                                                                          no                                  1           3     0     no                                  (n/a)                               (n/a)                               NULL 
Identity                                                                                                                         Seed                                     Increment                                Not For Replication 
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------- 
job_id                                                                                                                           1                                        1                                        0 
RowGuidCol                                                                                                           No rowguidcol column defined. 
Data_located_on_filegroup                                                                                                        
PRIMARY 
index_name                                                                                                                       index_description                                                                                                                                                                                                  index_keys                                                                                                         
PK__jobs__117F9D94                                                                                                               clustered, unique, primary key located on PRIMARY                                                                                                                                                                  job_id 
constraint_type                                                                                                                                    constraint_name                                                                                                                  delete_action update_action status_enabled status_for_replication constraint_keys                                                                                                                                                                                                 
CHECK on column max_lvl                                                                                                                            CK__jobs__max_lvl__145C0A3F                                                                                                      (n/a)         (n/a)         Enabled        Is_For_Replication     ([max_lvl] <= 250)
CHECK on column min_lvl                                                                                                                            CK__jobs__min_lvl__1367E606                                                                                                      (n/a)         (n/a)         Enabled        Is_For_Replication     ([min_lvl] >= 10)
DEFAULT on column job_desc                                                                                                                         DF__jobs__job_desc__1273C1CD                                                                                                     (n/a)         (n/a)         (n/a)          (n/a)                  ('New Position - title not formalized yet')
PRIMARY KEY (clustered)                                                                                                                            PK__jobs__117F9D94                                                                                                               (n/a)         (n/a)         (n/a)          (n/a)                  job_id 
Table is referenced by foreign key                                                                                                                                                                                                        
pubs.dbo.employee: FK__employee__job_id__1BFD2C07Table is referenced by views         
*/
这个够简单的吧

Answer 4

用 sp_help 表名可查看这个表的表名,字段,数据类型,字节,是否为空，约束，排序规则等

Answer 5

 --获得所有表的情况 
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 sysproperties g 
--on a.id=g.id AND a.colid = g.smallid 
order by object_name(a.id),a.colorder