Table columns

  • Thread starter Thread starter netnatter
  • Start date Start date
N

netnatter

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
 
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
 
After serious thinking netnatter wrote :
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
Click to expand...

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
 
Back
Top