Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    12

    Question Unanswered: sql_help tablename (Wrong Result)

    Hi All,

    I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.

    But if i see same table through QA using sp_help tablename
    then it will show length of my nvarchar column just double.

    when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.

    i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.

    can anybody tell where is the problem exaqctly ?

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    sp_help shows column length in bytes, where EM design view gives you number of character which will store in the column.

    sp_help uses 'int' data type for showing length, same as syscolumns uses 'smallint' data type for displaying length.

    nchar, nvarchar and ntext data types are Unicode data types & it will take two bytes for storing data. That's why it shows different figures in sp_help, syscolumns & EM design view.

    Refer Book OnLine for full information.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Sep 2006
    Posts
    12
    so if i want my correct data dictionary through below query then what to do
    ================================================== ====
    SELECT
    TOP 100 PERCENT obj.name AS table_name,
    cols.name AS field_name,
    type.name AS field_type,
    cols.length AS field_size,
    props.[value] AS field_description,
    cols.isnullable AS field_nullable,
    type.tdefault AS field_default

    FROM
    dbo.sysobjects obj
    INNER JOIN
    dbo.syscolumns cols ON obj.id = cols.id
    LEFT OUTER JOIN
    dbo.sysproperties props ON cols.id = props.id
    AND cols.colid = props.smallid
    LEFT OUTER JOIN
    dbo.systypes type ON cols.xtype = type.xusertype

    WHERE
    (obj.type = 'U')

    ORDER BY
    table_name

    ================================================== ======
    I want correct lenth for my nvarchar type in my data dictionary through this query?

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Check this... I have tested it.

    1. ntext datatype by default display 16 as length, we can not change it.
    2. xtype is physical storage type from systypes table.

    Code:
    SELECT
    TOP 100 PERCENT obj.name AS table_name, 
    cols.name AS field_name, 
    type.name AS field_type, 
    CASE  
         WHEN cols.xtype = 239 THEN cols.length/2 
         WHEN cols.xtype = 231 THEN cols.length/2 
         ELSE cols.length 
    END as field_size, 
    props.[value] AS field_description, 
    cols.isnullable AS field_nullable, 
    type.tdefault AS field_default
    
    FROM
    dbo.sysobjects obj 
    INNER JOIN
    dbo.syscolumns cols ON obj.id = cols.id 
    LEFT OUTER JOIN
    dbo.sysproperties props ON cols.id = props.id 
    AND cols.colid = props.smallid 
    LEFT OUTER JOIN
    dbo.systypes type ON cols.xtype = type.xusertype
    
    WHERE
    (obj.type = 'U')
    
    ORDER BY
    table_name
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    psutradhar,

    if you are looking for a good data dictionary generator, you might have a look at one I wrote, link is in my sig below.

  6. #6
    Join Date
    Sep 2006
    Posts
    12
    Hi,

    I am having SQL 2000 and .net framwork 1.1
    will your sql spec 3.1 will run on this configuration.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by psutradhar
    I am having SQL 2000 and .net framwork 1.1
    will your sql spec 3.1 will run on this configuration.
    SqlSpec requires .NET 2.0 on the client.

    It doesn't require any version of .NET installed on the server.

  8. #8
    Join Date
    Sep 2006
    Posts
    12
    hi,

    i downloaded your trial version and tried to install, even i installed .net framwork version 2 beta

    when i install your trial version it says

    install .net framwork vserioan v2.0.50727

    whats that? can u hlp me in install

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    You can download .NET 2.0 here - it's been out of beta ever since yukon shipped, which was a year ago:

    http://msdn2.microsoft.com/en-us/net.../aa731542.aspx

    pick one of the downloads appropriate for your cpu arch, where it says ".NET Framework Version 2.0 Redistributable Package"

Posting Permissions

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