Sysname 1

[Denaeghel]

I have a question about the types nvarchar and sysname. When I use the following query I get for nvarchar 2 types:

select sc.name as [name Field], st.name as [Name Type]
from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes st on st.xtype = sc.xtype
where so.Xtype = 'u'
and so.name = 'TableName'

Result:
Field1 nvarchar
Field1 sysname

But when I use the following stored procedure:
exec sp_MShelpcolumns 'TableName'

Result:
Field1 nvarchar

Why I get by the first query 2 results?

 

[JamesLean]

sysname is just an alias for nvarchar(128) - it's not technically a different type. If you look in the systypes table you'll see they both have the same xtype value.

Change your query to use the xusertype column of the systypes table when joining it to the syscolumns table and you will only get the one row in the result.

BTW, it is recommended you use the INFORMATION_SCHEMA views rather than query the sys tables directly for things like this:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'tablename'

--James

 

[Denaeghel]

Ok thanks James

That was the answer that I was looking for

Karel