Using Sp_columns in a procedure

  • hi

    im trying to get a tables columns using the sp_columns procedure

    somthing like this:

    select column_name from ( exec sp_columns table_name )

    - it doesnt work

    does anyone know a way to get a tables columns into a table that i can work with ?????

     

  • Try,

    sp_help tablename

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • Also, Try this

     

    SELECT c2.name FROM sysobjects c1 join syscolumns c2 on c1.id = c2.id where c1.name like 'your table name'

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • that was great

    thx 🙂

  • here is another one

    select * from information_schema.columns where TABLE_NAME = 'xxx'

    Cheers,

     


    * Noel

  • fantastic 🙂

  •  

    Not sure how you are using the column names from the query, but if you need

    them in a single string, you can do this:

     

    Declare @ColNames varchar(2000)

    select @ColNames = ''

    select @ColNames = @ColNames + sc.name + ', '

    from sysobjects so inner join syscolumns sc

    on so.id = sc.id

    where so.name = '<Table Name>'

    order by sc.ColOrder

     

    select @ColNames

     

     

  • thanks alot

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

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