Syscolumns not evaluate autoval

Question

0.00/5 (No votes)

See more:

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

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

SQL

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

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

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

Help me!!
Thanks

Posted 8-Oct-14 0:48am

equelna

Updated 8-Oct-14 1:45am

Herman<T>.Instance

v2

Add a Solution

Comments

CHill60 8-Oct-14 8:08am

I presume this table is the first table you have created on the 2005 instance, but the 131st table on the sql2000 instance?

2 solutions

Solution 1

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:

SQL

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
;

Posted 8-Oct-14 2:06am

Richard Deeming

Comments

CHill60 8-Oct-14 8:12am

D'oh. I missed that rather important fact! My 5.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

equelna · Accepted Answer · 2014-10-08T05:02:00

Solution 2

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

Posted 8-Oct-14 5:02am

equelna

Syscolumns not evaluate autoval

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Syscolumns not evaluate autoval

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us