Table columns

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
 
R

rote

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
 
H

Hans Kesting

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

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
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top