Table columns

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

  1. netnatter

    netnatter Guest

    My asp.net 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
     
    netnatter, Sep 30, 2008
    #1
    1. Advertisements

  2. netnatter

    Alex Meleta Guest

    Hi netnatter,

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

    Regards, Alex Meleta
    [TechBlog] http://devkids.blogspot.com
     
    Alex Meleta, Sep 30, 2008
    #2
    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))

    SET NOCOUNT ON

    INSERT #temp

    EXEC sp_msforeachtable 'sp_spaceused ''?'''

    SELECT a.table_name,

    a.row_count,

    COUNT(*) AS col_count,

    a.data_size

    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

    Patrick
     
    rote, Sep 30, 2008
    #3
  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
    #4
    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.