Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: systypes gone nuts

    Hi,

    I have created this simple table
    Code:
    create table asset(
    	id	integer		not null,
    	name	nvarchar(50)	not null,
    	constraint pk_asset primary key (id)
    );
    When I query the system tables about this "asset" table with
    Code:
    SELECT	b.name,  c.name as TypeName, 
    	b.length,  b.isnullable, 
    	b.collation, b.xprec, 
    	b.xscale
    FROM sysobjects a 
    	inner join syscolumns b on 
    		a.id = b.id 
    	inner join systypes c on 
    		b.xtype = c.xtype and c.name <> 'sysname' 
    WHERE a.id = object_id(N'[dbo].[asset]') 
    	and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 
    ORDER BY b.colId
    I get this result:
    Code:
    name	TypeName	length	isnullable	collation	xprec	xscale
    id	int	4	0	NULL	10	0
    name	nvarchar	100	0	SQL_Latin1_General_CP1_CI_AS	0	0
    name	AccountNumber	100	0	SQL_Latin1_General_CP1_CI_AS	0	0
    name	Name	100	0	SQL_Latin1_General_CP1_CI_AS	0	0
    name	OrderNumber	100	0	SQL_Latin1_General_CP1_CI_AS	0	0
    name	Phone	100	0	SQL_Latin1_General_CP1_CI_AS	0	0
    Showing rogue TypeNames AccountNumber, Name, OrderNumber and Phone. When I ran this script on my other tables, those 4 TypeNames appear in ALL of them.

    They look like column names from other tables. They appear in dbo.systypes. I think they must be user defined data types or something like that. But why do they appear in the resultset of my query? How can I get rid of them?

    The database I'm using is the sample database AdventureWorks that came with SQL Server 2005
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Feb 2009
    Posts
    2
    Hi, Those are the Windows Collation Designators

    You can read about them here
    Windows Collation Designators

    Make sure you select the sql server collations

    hope this helps

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    try like this
    select *
    from

    (SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale,

    ROW_NUMBER() OVER (PARTITION BY b.name ORDER BY b.colid) AS ran

    FROM

    sysobjects a

    INNER JOIN

    syscolumns b ON a.id = b.id

    INNER JOIN

    systypes c ON b.xtype = c.xtype AND c.name <> 'sysname'

    WHERE a.id = object_id(N'dbo.asset')

    and OBJECTPROPERTY(a.id, N'IsUserTable') = 1

    ) as a where a.ran = 1

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    try this just change the xusertype
    SELECT b.name, c.name as TypeName,
    b.length, b.isnullable,
    b.collation, b.xprec,
    b.xscale
    FROM sysobjects a
    inner join syscolumns b on
    a.id = b.id
    inner join systypes c on
    b.xtype = c.xusertype and c.name <> 'sysname'
    WHERE a.id = object_id(N'[dbo].[access]')
    and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
    ORDER BY b.colId

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It's not the collation that is bothering me. It are the "data types" AccountNumber, Name, OrderNumber and Phone that appear in the result. It means my INNER J0IN with the system tables is somewhere faulty, but I can't find where.

    This is a cleaned up version of the result set:
    Code:
    name	dataType	length	
    id	int	4
    name	nvarchar	100
    name	AccountNumber	100
    name	Name	100
    name	OrderNumber	100
    name	Phone	100
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your joins look fine to me. Perhaps these are user-defined datatypes.
    What does "select * from systypes" get you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by blindman
    Your joins look fine to me. Perhaps these are user-defined datatypes.
    What does "select * from systypes" get you?
    This in an excerpt of the result set:
    Code:
    ...
    name	xtype 	 	 	 	 	 	 	 	uid	 	 	 	 	 	 	  	
    char	175	0	175	8000	0	0	0	0	4	0	872468488	1	0	1	47	NULL	8000	NULL	SQL_Latin1_General_CP1_CI_AS
    timestamp	189	1	189	8	0	0	0	0	4	0	NULL	80	0	0	45	NULL	8	NULL	NULL
    ...
    AccountNumber 231	0	257	30	0	0	0	0	1	0	872468488	257	1	1	39	NULL	15	NULL	SQL_Latin1_General_CP1_CI_AS
    Flag	104	1	258	1	1	0	0	0	1	0	NULL	258	0	0	50	NULL	1	NULL	NULL
    NameStyle	104	1	259	1	1	0	0	0	1	0	NULL	259	0	0	50	NULL	1	NULL	NULL
    Name	231	0	260	100	0	0	0	0	1	0	872468488	260	1	1	39	NULL	50	NULL	SQL_Latin1_General_CP1_CI_AS
    OrderNumber	231	0	261	50	0	0	0	0	1	0	872468488	261	1	1	39	NULL	25	NULL	SQL_Latin1_General_CP1_CI_AS
    Phone	231	0	262	50	0	0	0	0	1	0	872468488	262	1	1	39	NULL	25	NULL	SQL_Latin1_General_CP1_CI_AS
    Their UID is all 1, while for all the system data types like char and timestamp, it's 4.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    bklr,
    Thank you for your suggestions.

    This one (with xusertype) returns nothing.

    Your previous suggestion (with (PARTITION BY) resulted in the correct dataset. But I don't understand why...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Dec 2008
    Posts
    135
    when u use the select * from systypes u r getting more no of rows
    but in ur table ur using some datatypes int,nvarchar but when it joins it will give u the result with the systypes so that i used xusertype in that join

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    try this just change the xusertype
    The query that followed this sentence is not working properly. It returns 0 records. I should get 2 records: "id" and "name".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Wim
    Their UID is all 1, while for all the system data types like char and timestamp, it's 4.
    That's a little odd. All the native datatypes typically belong to userid 1, which is normally dbo.
    Which user is the owner of the database, and what does "select * from sysusers" show you for users 1 and 4?

    In the meantime, trying join on b.xtype = c.xusertype if you want to avoid the duplicates.
    Last edited by blindman; 02-13-09 at 10:49.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Wim
    The query that followed this sentence is not working properly. It returns 0 records. I should get 2 records: "id" and "name".
    That's because his sample code referred to a table named "[access]", rather than your table "[asset]". Just change the table name and it will work.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is the code that I managed to get working in the mean time. I tweaked it a bit.
    Code:
    SELECT c.name, s.name AS TypeName, c.length, c.isnullable, c.collation, c.xprec, c.xscale
    FROM dbo.sysobjects AS o
    	INNER JOIN dbo.syscolumns AS c ON 
    		c.id = o.id
    	INNER JOIN dbo.systypes AS s ON 
    		c.xtype = s.xusertype
    WHERE o.type = 'U' and
    	o.id = object_id(N'[dbo].[asset]')
    order by c.colId
    Indeed, the name of the table ... I didn't noticed. Your new name will be Hawkeye, Blindman!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If only I were as adept at finding my own mistakes as I am at those of others....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2008
    Posts
    135
    hi wim,
    try this
    SELECT b.name, c.name as TypeName,
    b.length, b.isnullable,
    b.collation, b.xprec, b.xscale
    FROM sysobjects a
    inner join syscolumns b on
    a.id = b.id
    inner join systypes c on
    b.xtype = c.xtype and b.xusertype = c.xusertype WHERE a.id = object_id(N'[dbo].[emptable]')
    and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
    ORDER BY b.colId

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •