none
count number of columns in a table RRS feed

  • Question

  • I need to know the number of columns in a table as I will be using this number to in a loop in a Dynamic SQL.

    e.g.

    declare x int

    x= 'some code that generates number of columns in a table'

    while i<=x
    begin
    some code
    some code
    ...
    i=i+1
    end

    Many thanks

    Tuesday, August 25, 2009 1:40 PM

Answers

  • after a bit of googling i came to this conclusion and it works

    SET @TABLE = 'table name'
    
    SET @NO_OF_COLUMNS=(
    		  SELECT COUNT(*)
    		  FROM INFORMATION_SCHEMA.COLUMNS
    		  WHERE TABLE_NAME = @TABLE
    		 )
    
    • Marked as answer by zx8754 Wednesday, August 26, 2009 11:05 AM
    Wednesday, August 26, 2009 11:04 AM

All replies

  • Hi,
     Pls try this code
    select count(*) Noofcolumns from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='table_name')
    Tuesday, August 25, 2009 4:54 PM
  • after a bit of googling i came to this conclusion and it works

    SET @TABLE = 'table name'
    
    SET @NO_OF_COLUMNS=(
    		  SELECT COUNT(*)
    		  FROM INFORMATION_SCHEMA.COLUMNS
    		  WHERE TABLE_NAME = @TABLE
    		 )
    
    • Marked as answer by zx8754 Wednesday, August 26, 2009 11:05 AM
    Wednesday, August 26, 2009 11:04 AM