你要这个?select * from syscolumns where id=object_id('TableName')
--不太好实现!看看这个行不行? create table test1 (id int,name varchar(10)) ------------------------------------------- Select A.name as Column_name,A.Length,B.Name as Type, IsNullable=case A.isnullable when 0 then 'yes' else 'no' end From syscolumns A Inner Join Systypes B on A.xtype=B.xtype where id=Object_id('test1')
一楼的效果不理想,想要sp_help返回的那种格式二楼的看不大懂...
--真要看那么多,直接把SP_Help的相关语句剥离出来就可以用了declare @objname nvarchar(776) select @objname='TableName' declare @no varchar(35), @yes varchar(35), @none varchar(35) select @no = name from master.dbo.spt_values where type = 'B' and number = 0 select @yes = name from master.dbo.spt_values where type = 'B' and number = 1 select @none = name from master.dbo.spt_values where type = 'B' and number = 2 declare @numtypes nvarchar(80) select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney' declare @objid int declare @sysobj_type char(2) select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)select 'Column_name' = name, 'Type' = type_name(xusertype), 'Computed' = case when iscomputed = 0 then @no else @yes end, 'Length' = convert(int, length), 'Prec' = case when charindex(type_name(xtype), @numtypes) > 0 then convert(char(5),ColumnProperty(id, name, 'precision')) else ' ' end, 'Scale' = case when charindex(type_name(xtype), @numtypes) > 0 then convert(char(5),OdbcScale(xtype,xscale)) else ' ' end, 'Nullable' = case when isnullable = 0 then @no else @yes end, 'TrimTrailingBlanks' = case ColumnProperty(@objid, name, 'UsesAnsiTrim') when 1 then @no when 0 then @yes else '(n/a)' end, 'FixedLenNullInSource' = case when type_name(xtype) not in ('varbinary','varchar','binary','char') Then '(n/a)' When status & 0x20 = 0 Then @no Else @yes END, 'Collation' = collation from syscolumns where id = @objid and number = 0 order by colid
SELECT 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 '1' else '0' 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 '1'else '0' 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 sysproperties g on a.id=g.id AND a.colid = g.smallid where d.name='tmpacc' order by a.id,a.colorder
打忘了where d.name='tmpacc' 改成 where d.name='你的表名'
declare @objname nvarchar(776) select @objname='TableName'把这段改成Create Proc pGetTableColumns @objname sysname As
Create Proc pGetTableColumns @objname sysname AsSELECT 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 '1' else '0' 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 '1'else '0' 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 sysproperties g on a.id=g.id AND a.colid = g.smallid where d.name=@objname order by a.id,a.colorder试试看
create table test1 (id int,name varchar(10))
-------------------------------------------
Select A.name as Column_name,A.Length,B.Name as Type,
IsNullable=case A.isnullable when 0 then 'yes' else 'no' end
From syscolumns A Inner Join Systypes B
on A.xtype=B.xtype
where id=Object_id('test1')
select @objname='TableName'
declare @no varchar(35), @yes varchar(35), @none varchar(35)
select @no = name from master.dbo.spt_values where type = 'B' and number = 0
select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
select @none = name from master.dbo.spt_values where type = 'B' and number = 2
declare @numtypes nvarchar(80)
select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
declare @objid int
declare @sysobj_type char(2)
select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)select
'Column_name' = name,
'Type' = type_name(xusertype),
'Computed' = case when iscomputed = 0 then @no else @yes end,
'Length' = convert(int, length),
'Prec' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(id, name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),OdbcScale(xtype,xscale))
else ' ' end,
'Nullable' = case when isnullable = 0 then @no else @yes end,
'TrimTrailingBlanks' = case ColumnProperty(@objid, name, 'UsesAnsiTrim')
when 1 then @no
when 0 then @yes
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When status & 0x20 = 0 Then @no
Else @yes END,
'Collation' = collation
from syscolumns where id = @objid and number = 0 order by colid
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 '1' else '0' 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 '1'else '0' 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 sysproperties g
on a.id=g.id AND a.colid = g.smallid
where d.name='tmpacc'
order by a.id,a.colorder
改成
where d.name='你的表名'
select @objname='TableName'把这段改成Create Proc pGetTableColumns
@objname sysname
As
@objname sysname
AsSELECT
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 '1' else '0' 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 '1'else '0' 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 sysproperties g
on a.id=g.id AND a.colid = g.smallid
where d.name=@objname
order by a.id,a.colorder试试看
因为我是用ADO来调用SQL数据,ADO里有一个NextRecordset方法可以滚动对象中的记录集
很方便的
再次谢谢大家,结贴!