Get the Table Structure using a simple query

  • Comments posted to this topic are about the item Get the Table Structure using a simple query

    Mohit Nayyar
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

  • Dear Mohit,

    I have executed the SQL query. It is giving the exact output that you are saying.

    I found one issue in the output information. if you look it the datatype, it is showing one strange datatype name sysdata in the table structure actually in should not come.

    can you please look into it and change the query?



  • U can get better information in the following query:


    'Datatype'=Case When in ('char','varchar') Then'('+Cast(Col.max_length as varchar(10))+')'

    When = 'nvarchar' AND Col.max_length<>-1 Then'('+Cast(Col.max_length/2 as varchar(10))+')'

    When = 'nvarchar' AND Col.max_length=-1 Then'(MAX)'

    When = 'decimal' Then'('+Cast(Col.precision as varchar(5))+','+Cast(Col.scale as varchar(5))+')'


    End, 'Length'=Col.max_length, 'isNullable'=Case When Col.is_nullable = 1 Then 'Yes' Else 'No' End,

    'isIdentity'=Case When Col.is_identity = 1 Then 'Yes' Else 'No' End, Col.column_id

    From sys.columns Col

    Inner Join sys.sysobjects sysObj ON = Col.object_id

    Inner Join sys.types typ ON typ.user_type_id = Col.user_type_id

    Where sysObj.xtype = 'U'

    and like 'ma_Parts%'

    order by, Col.column_id;

  • I am looking for this query. Thanks


  • SELECT Table_Name, Column_Name, DataType, c.Length, CASE WHEN c.isnullable=1 THEN 'Yes' ELSE 'No' END AS 'Nullable'

    FROM sysobjects o

    INNER JOIN syscolumns c ON ( and o.type='U' and not like 'dt%')

    Inner Join systypes t ON (c.xtype=t.xtype)


    order by, c.colorder

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply