Table columns

Discussion in 'ASP .Net' started by netnatter, Sep 30, 2008.

  1. netnatter

    netnatter Guest

    My pages allow the user to add additional columns to an SQL table

    Is there an SQL statement that I can use to return the number of columns
    that the table currently has?

    netnatter, Sep 30, 2008
    1. Advertisements

  2. netnatter

    Alex Meleta Guest

    Hi netnatter,

    Starting investigation from 'SELECT COUNT(*) FROM <table>' might be a good

    Regards, Alex Meleta
    Alex Meleta, Sep 30, 2008
    1. Advertisements

  3. netnatter

    rote Guest

    run this below againts your database:
    CREATE TABLE #temp (

    table_name sysname ,

    row_count INT,

    reserved_size VARCHAR(50),

    data_size VARCHAR(50),

    index_size VARCHAR(50),

    unused_size VARCHAR(50))


    INSERT #temp

    EXEC sp_msforeachtable 'sp_spaceused ''?'''

    SELECT a.table_name,


    COUNT(*) AS col_count,


    FROM #temp a

    INNER JOIN information_schema.columns b

    ON a.table_name collate database_default

    = b.table_name collate database_default

    GROUP BY a.table_name, a.row_count, a.data_size

    ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

    DROP TABLE #temp

    hope that helps

    rote, Sep 30, 2008
  4. netnatter

    Hans Kesting Guest

    After serious thinking netnatter wrote :
    SELECT * FROM <table> WHERE 1=0

    this doesn't return any rows, but does return the structure of the
    table. Fill a DataSet with it and you can investigate the Columns.

    Hans Kesting
    Hans Kesting, Sep 30, 2008
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.