34,590
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
sdhdy 2009-04-20
- 打赏
- 举报
--********************************************************************************************
--************ 表字段大全 整理：sdhdy 日期：2004-01-01 *******************
--********************************************************************************************
SELECT
表名=d.name,
表说明=isnull(f.value,''),
字段序号=a.colorder,
字段名=a.name,
标识=case when a.colstat=1 then '√'else '' end,
主键=case when exists(select 1 from sysindexes y,sysindexkeys z where y.id=z.id and y.indid=z.indid and z.id=a.id and z.colid=a.colid and y.status & 2948=2048) then '√'else '' end,
类型=b.name,
占用字节数=a.length,
精度=a.prec,
小数位数=isnull(a.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 a.id=f.id and f.smallid=0
where d.name='tablename' --如果只查询指定表,加上此条件
order by a.id,a.colorder 
taoistong 2009-04-20
- 打赏
- 举报
数据类型为“自动编号”的
taoistong 2009-04-20
- 打赏
- 举报
选择表
右键 ->设计视图
右键 ->设计视图
lanman 2009-04-20
- 打赏
- 举报
Access的怎么看？
taoistong 2009-04-20
- 打赏
- 举报
ACCESS可以查看表的设计，列的数据类型为自动编号的
htl258_Tony 2009-04-20
- 打赏
- 举报
建表语句在前.
让你望见影子的墙 2009-04-20
- 打赏
- 举报
SELECT TableName=o.name,OWNER=USER_NAME(o.uid),TableDescription=ISNULL(ptb.value,N''),
FieldId=c.colid,FieldName=c.name,
FieldType=QUOTENAME(t.name)
+CASE
WHEN t.name IN (N'decimal',N'numeric')
THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
WHEN t.name=N'float'
OR t.name like N'%char'
OR t.name like N'%binary'
THEN N'('+CAST(c.prec as varchar)+N')'
ELSE N'' END
+CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END+N' NULL',
FieldDescription=ISNULL(pfd.value,''),
DefileLength=c.length,
FieldDefault=ISNULL(df.text,N''),
IsIDENTITY=COLUMNPROPERTY(o.id,c.name,N'IsIdentity'), ----这块
IsComputed=COLUMNPROPERTY(o.id,c.name,N'IsComputed'),
IsROWGUID=COLUMNPROPERTY(o.id,c.name,N'IsRowGuidCol'),
IsPrimaryKey=CASE WHEN opk.xtype IS NULL THEN 0 ELSE 1 END
FROM sysobjects o
JOIN syscolumns c
ON c.id=o.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
JOIN systypes t
ON t.xusertype=c.xusertype
LEFT JOIN syscomments df
ON df.id=c.cdefault
LEFT JOIN sysproperties ptb
ON ptb.id=o.id and ptb.smallid=0
LEFT JOIN sysproperties pfd
ON pfd.id=o.id and pfd.smallid=c.colid
LEFT JOIN sysindexkeys idxk
ON idxk.id=o.id
AND idxk.colid=c.colid
LEFT JOIN sysindexes idx
ON idx.indid=idxk.indid
AND idx.id=idxk.id
AND idx.indid NOT IN(0,255)
LEFT JOIN sysobjects opk
ON opk.parent_obj=o.id
AND opk.name=idx.name
AND OBJECTPROPERTY(opk.id,N'IsPrimaryKey')=1
ORDER BY o.name,c.colidhtl258_Tony 2009-04-20
- 打赏
- 举报
SQL:
select * from syscolumns where id=object_id(N'aaa') and COLUMNPROPERTY(id,name,'IsIdentity')=1
create table aaa(fid int identity,a int)
/*
name id xtype typestat xusertype length xprec xscale colid xoffset bitpos reserved colstat cdefault domain number colorder autoval offset collationid language status type usertype printfmt prec scale iscomputed isoutparam isnullable collation tdscollation
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- -------- --------- ------ ----- ------ ------ ------- ------ -------- ------- ----------- ----------- ------ -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------ ----------- ----------- ------ ---- -------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ------------
fid 1536724527 56 1 56 4 10 0 1 0 0 0 1 0 0 0 1 NULL 2 0 0 128 56 7 NULL 10 0 0 0 0 NULL 0x0000000000
(1 行受影响)
*/
drop table aaataoistong 2009-04-20
- 打赏
- 举报
SQL，中，用sp_helptext +表
执行可以查看
执行可以查看
htl258_Tony 2009-04-20
- 打赏
- 举报
[Quote=引用 10 楼 lanman 的回复:]
引用 7 楼 taoistong 的回复:
选择表
右键 ->设计视图
晕，我指的当然是用SQL语句来看了！！
[/Quote]
这个问题最到到ACCESS版问一下,那边有高人.
引用 7 楼 taoistong 的回复:
选择表
右键 ->设计视图
晕，我指的当然是用SQL语句来看了！！
[/Quote]
这个问题最到到ACCESS版问一下,那边有高人.
taoistong 2009-04-20
- 打赏
- 举报
没用过
lanman 2009-04-20
- 打赏
- 举报
[Quote=引用 7 楼 taoistong 的回复:]
选择表
右键 ->设计视图
[/Quote]
晕，我指的当然是用SQL语句来看了！！
选择表
右键 ->设计视图
[/Quote]
晕，我指的当然是用SQL语句来看了！！
