Syscolumns不评估autoval [英] Syscolumns not evaluate autoval

问题描述

我在sqlserver 2005上使用sqlserver 2000生成的sql脚本在db中创建了一个表。

Hi,
I have create a table in a db on sqlserver 2005 using sql script generate by a sqlserver 2000.

<pre lang="SQL">
CREATE TABLE [dbo].[TblTransazioni] (
	[IDREC] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY  CLUSTERED,
	[DataMov] [datetime] NOT NULL ,
	[IDOrdine] [varchar] (10)  NOT NULL ,
	[Checked] [smallint] NOT NULL default(0)
) ON [PRIMARY]
GO

表中列IDREC声明了选项IDENTITY PRYMARY KEY。

当我使用查询时

The table have column IDREC declared with options IDENTITY PRYMARY KEY.
When I use the query

Select a.Name, isnullable, TypeName=(b.name),IsIdentityCol=cast(isnull(autoval,0) as bigint) 
from syscolumns A inner join systypes B on a.xtype=b.xtype where id=object_id('TblTransazioni') order by colid

结果是：

the result is:

Name                  isnullable  TypeName            IsIdentityCol
--------------------- ----------- ------------------- --------------
IDREC                 0           bigint              0
DataMov               0           datetime            0
IDOrdine              0           varchar             0
Checked               0           smallint            0

当我执行相同的查询usimgi sqlserver 2000时，结果是

When I execute same query usimgi sqlserver 2000 then result is

Name                  isnullable  TypeName            IsIdentityCol
--------------------- ----------- ------------------- --------------
IDREC                 0           bigint              130
DataMov               0           datetime            0
IDOrdine              0           varchar             0
Checked               0           smallint            0

错误在哪里？

I'我很生气，我很兴奋



帮帮我!!

谢谢

Where is the mistake ?
I'm very angry and I was thrilled

Help me!!
Thanks

推荐答案

syscolumns 表中的 autoval 列是仅供内部使用。从SQL 2005开始，此列始终返回 NULL 。



尝试使用 sys.columns 视图 [ ^ ]而不是：

The autoval column on the syscolumns table is "for internal use only". Since SQL 2005, this column always returns NULL.

Try using the sys.columns view[^] instead:

SELECT 
    C.name, 
    C.is_nullable As isnullable, 
    T.name As TypeName,
    C.is_identity As IsIdentityCol
FROM 
    sys.columns As C
    INNER JOIN sys.types As T
    ON T.system_type_id = C.system_type_id
WHERE 
    C.object_id = OBJECT_ID('TblTransazioni')
ORDER BY
    C.column_id
;

感谢

工作正常



是的，在SQL2000上可能是第130个表，我用它来测试，因为有很少的字段

Thank
It works fine

Yes, it's possible that on SQL2000 is the 130th table, I have used that to test because have few fields

这篇关于Syscolumns不评估autoval的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！