Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Table Descriptions

    I am using SQL Query Analyzer 7.0. What I would like to do is obtain table/ attribute descriptions i.e. field length, data type.

    In Oracle a desc TABLENAME command would generally be used can anyone help with the SQL Query analyzer equivalent.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Table Descriptions

    Originally posted by markvenis
    I am using SQL Query Analyzer 7.0. What I would like to do is obtain table/ attribute descriptions i.e. field length, data type.

    In Oracle a desc TABLENAME command would generally be used can anyone help with the SQL Query analyzer equivalent.
    you can use:
    sp_help Table_name

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    sp_help

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    Re: Table Descriptions

    Originally posted by harshal_in
    you can use:
    sp_help Table_name

    This is close but what i relly need to know is the field length and data type of the fields in the table.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    create proc sp_desc_table
    @objname nvarchar(776) = NULL -- object name we're after

    as
    declare @objid int
    -- PRELIMINARY
    set nocount on
    declare @numtypes nvarchar(80)
    select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,nu meric,smallmoney'
    select @objid = id from sysobjects where id = object_id(@objname)
    declare @no varchar(35), @yes varchar(35), @none varchar(35)
    select @no = name from master.dbo.spt_values where type = 'B' and number = 0
    select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
    select @none = name from master.dbo.spt_values where type = 'B' and number = 2
    select
    'Column_name' = name,
    'Type' = type_name(xusertype),
    'Computed' = case when iscomputed = 0 then @no else @yes end,
    'Length' = convert(int, length),
    'Prec' = case when charindex(type_name(xtype), @numtypes) > 0
    then convert(char(5),ColumnProperty(id, name, 'precision'))
    else ' ' end,
    'Scale' = case when charindex(type_name(xtype), @numtypes) > 0
    then convert(char(5),OdbcScale(xtype,xscale))
    else ' ' end,
    'Nullable' = case when isnullable = 0 then @no else @yes end,
    'TrimTrailingBlanks' = case ColumnProperty(@objid, name, 'UsesAnsiTrim')
    when 1 then @no
    when 0 then @yes
    else '(n/a)' end,
    'FixedLenNullInSource' = case
    when type_name(xtype) not in ('varbinary','varchar','binary','char')
    Then '(n/a)'
    When status & 0x20 = 0 Then @no
    Else @yes END,
    'Collation' = collation
    from syscolumns where id = @objid and number = 0 order by colid



    here you go ...
    Give the table name as parameter to the sp ...

Posting Permissions

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