August 9, 2005 at 2:52 pm
For example, when you run the following script in Northwind, it tells you every column has more than one data type, and for type of varchar/nvarchar, it doubles the size!
Why? which data type is the real one? Thanks.
select
distinct so.name as [Table Name],
sc.colid as [ID],
sc.name as [Column Name],
st.name as [Data Type],
sc.length as [Size]
from sysobjects so
inner join syscolumns sc on sc.id = so.id
inner join systypes st on sc.type = st.type
--inner join sysproperties sp on (so.id = sp.id and sp.smallid = sc.colid and sp.type = 4)
where so.id = (select id from sysobjects where name = 'Customers')
August 10, 2005 at 12:45 am
Join on xusertype instead and you will get the correct result:
select
distinct so.name as [Table Name],
sc.colid as [ID],
sc.name as [Column Name],
st.name as [Data Type],
sc.length as [Size]
from sysobjects so
inner join syscolumns sc on sc.id = so.id
inner join systypes st on sc.xusertype = st.xusertype
--inner join sysproperties sp on (so.id = sp.id and sp.smallid = sc.colid and sp.type = 4)
where so.id = (select id from sysobjects where name = 'Customers')
If you do not already know it, the system stored procedure sp_help gives similar information.
August 10, 2005 at 7:17 am
Also one other thing, if you have a user defined datatype you will get 2 rows for those columns... unless you didn't build the query exactly as I did back then...
August 10, 2005 at 7:26 am
Thank you guys. I still have question: why the revised query returns incorrect size for nchar and nvarchar? e.g. in Northwind, the CompanyName is nvarcahr(40), this script will return 80, and so on....Is that the difference between nvarchar and varchar?
August 10, 2005 at 7:33 am
"n" implies unicode and will always use 2 bytes for each one of its' "non-n" counterpart...
**ASCII stupid question, get a stupid ANSI !!!**
August 10, 2005 at 11:42 pm
Like sushila says, unicode means 2 bytes per character. Length is not the number of characters stored, but the number of bytes used for storage. E.g. and int shows 4 in the Length column.
August 12, 2005 at 3:39 am
You might find it easier to use the "Information.Schema" views to get your structure e.g.
SELECT
TABLE_NAME [Table Name],
ORDINAL_POSITION [ID],
COLUMN_NAME [Column Name],
DATA_TYPE [Data Type],
CHARACTER_MAXIMUM_LENGTH [Size]
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME='Customers'
James Horsley
Workflow Consulting Limited
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply