Writing sql to show the structure of a table

  • Hello,

    I am using Query Analyzer instead of Enterprise Manager.

    I need an example of a sql script that shows the structure of the table (ie, col name, size and type).

    Thanks

    Indy

     

     

     

  • Is there something you're looking for that running an "sp_help" won't give you?

    Mark

  • sp_help is exactly what I was looking for.

    Thanks

    Indy

     

  • you can also use INFORMATION_SCHEMA.COLUMNS

  • Or you can use this that was posted on the site as an article a little while ago:

    Select

    'Table Name ' = B.name,

    'Column name' = A.name,

    'Column No'   = colid,

    'Type'        = type_name (xusertype),

    'Length'      = convert (int, length),

    'Allow Nulls'= case when isnullable = 0 then 'No' else 'Yes' end

    From syscolumns A, sysobjects B

    Where A.id=B.id and B.type ='U' and A. number=0 and B.Status > 0

    AND B.[Name] = YourTableName

    Order by b.name, colid

    Obviously replace the "YourTableName" for well... yeah... you get it!

  • If you like a table definintion that can be printed on the width of a page and saved as text that can be imported into documentation, I have the following stored procedure that I wrote (with the help of postings on this site).  It includes the description you store with each field and the table.  Too long of a description or a period in the description creates a line feed so it will fit on a page.  You can tailor it to the way you want to see your documentation.

    After creating, you can run it from Query Analyzer with results to text and then save the results to a file with a .txt extension.

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    create proc usp_Table_Doc

     @tblname varchar(50)

    as

    if @tblname is null begin

        raiserror(15250,-1,-1)

        return(1)

    end

    -- validate @tblname

    declare @id int,@dbname sysname,@type char(2)

    if @tblname is not null begin

        select @dbname = parsename(@tblname, 3)

        if @dbname is not null and @dbname <> db_name()

     begin

         raiserror(15250,-1,-1)

         return (1)

     end

     if @dbname is null

         select @dbname = db_name()

     /*

     **  Try to find the object.

     */

     select @id = null

     select @id = id, @type = xtype

      from sysobjects

       where id = object_id(@tblname)

     /*

     **  Does the object exist?

     */

     if @id is null

         begin

      raiserror(15009,-1,-1,@tblname,@dbname)

      return (1)

         end

    end

    declare @tabledesc sql_variant, @tabdes varchar(500),

     @headtemp varchar(70), @headlen tinyint, @i tinyint,

     @j tinyint

    SELECT @tabledesc = value

     FROM   ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, DEFAULT, DEFAULT)

    if @tabledesc is not null

       select @tabdes = cast(@tabledesc as varchar(500))

    else

       select @tabdes = ' '

    select @headlen = 88 - (17 + len(@tblname))

    select @i = @headlen

    if len(@tabdes) > @headlen

        begin

        while substring(@tabdes,@i,1) <> ' '

     select @i = @i - 1

        end

    select @headtemp = left(@tabdes,@i)

    print '========================================================================================'

    print '------'

    print '------ Table: '+@tblname+ ' - ' + @headtemp

    while len(@tabdes) > @headlen

        begin

     select @tabdes = substring(@tabdes,@i + 1,len(@tabdes))

     select @i = @headlen

     if len(@tabdes) > @headlen

         begin

         while substring(@tabdes,@i,1) <> ' '

      select @i = @i - 1

         end

     select @headtemp = left(@tabdes,@i)

     print '------'+space(88-6-@headlen)+@headtemp

        end

    print '------'

    declare @sqltext varchar(8000)

        select @sqltext=

     'declare tblstru_crsr cursor for

     select a.name,a.xusertype,a.length,a.xprec,a.xscale,a.isnullable from syscolumns a,sysobjects b

     where b.name like '''+@tblname+''' and b.id=a.id order by colid'

    exec (@sqltext)

    declare @name varchar(21),@xusertype smallint,

     @length smallint,@vcLen varchar(4),@xprec tinyint,

     @xscale tinyint,@vcSca varchar(3),@isnullable int

    declare @namedesc varchar(21),@typedesc varchar(14),

     @nulldesc varchar(4),@lendesc varchar(13),

     @description sql_variant,@desc varchar(500),

     @desctemp varchar(38)

    open tblstru_crsr

    fetch tblstru_crsr into @name,@xusertype,@length,@xprec,@xscale,@isnullable

    set nocount on

    print 'Column Name          Type       Length Scale Null Description'

    while @@fetch_status= 0 begin

        print '-------------------- ---------- ------ ----- ---- --------------------------------------'

     -- right fill with spaces 

     select @namedesc = @name+space(21-len(@name))

     SELECT @typedesc = left(name,12) FROM systypes

     WHERE xtype in (SELECT xtype FROM systypes

      WHERE xusertype = @xusertype) and xtype = xusertype

     -- Proper case and right fill with spaces

     select @typedesc = upper(left(@typedesc,1))+substring(@typedesc,2,len(@typedesc))+space(12-len(@typedesc))

     -- Right Justify Numbers within fields

     if @xprec > 0

         begin

      select @vcLen = ltrim(str(@xprec))

      select @vcLen = space(4-len(@vcLen))+@vcLen

      select @vcSca = ltrim(str(@xscale))

      select @vcSca = space(3-len(@vcSca))+@vcSca

      select @lendesc = @vcLen+'   '+@vcSca+'   '

         end

     else

         begin

      select @vcLen = ltrim(str(@length))

      select @vcLen = space(4-len(@vcLen))+@vcLen

      select @lendesc = @vcLen+space(9)

         end

     if @isnullable = 1

         select @nulldesc = 'YES '

     else

         select @nulldesc = 'NO  '

     SELECT @description = value

      FROM   ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, N'column', @name)

     if @description is not null

        select @desc = cast(@description as varchar(500))

     else

        select @desc = ' '

     

     if len(@desc) > 38

         select @i = 39

     else

         select @i = len(@desc)

         begin

             while substring(@desc,@i,1) <> ' ' and @i < len(@desc)

          select @i = @i - 1

         select @j-2 = 1

         while substring(@desc,@j,1) <> '.' and

        @i > @j-2

             select @j-2 = @j-2 + 1

         select @i = @j-2

            end

     select @desctemp = left(@desc,@i)

     print @namedesc+@typedesc+@lendesc+@nulldesc+@desctemp

     while len(@desc) > @i

         begin

      select @desc = substring(@desc,@i + 1,len(@desc))

      if len(@desc) > 38

          select @i = 39

      else

          select @i = len(@desc)

          begin

              while substring(@desc,@i,1) <> ' ' and @i < len(@desc)

           select @i = @i - 1

          select @j-2 = 1

          while substring(@desc,@j,1) <> '.' and

            @i > @j-2

          select @j-2 = @j-2 + 1

          select @i = @j-2

          end

     

      select @desctemp = left(@desc,@i)

      print space(50)+@desctemp

         end

     fetch tblstru_crsr into @name,@xusertype,@length,@xprec,@xscale,@isnullable

    end

    close tblstru_crsr

    deallocate tblstru_crsr

    print '========================================================================================'

    return

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • Ummmm.... press the {f8} function key...

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • is it possible to get the structure of a temporary table already in memory?

  • exec tempdb..sp_help #tablename

Viewing 9 posts - 1 through 8 (of 8 total)

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