how to get the all the column names of a table?

Can't figure out...

Given a database table name, how do I get all the column names of the table? Actually my task is more than that, what I want is the percentage of null values for each column. But there too many columns in the table and I don't want to type the column name one by one. How to achieve it?

For example, suppose I have a table TB which contains 50 columns C1 to C50. What I want to produce a stored-procedure that table the table name as input parameter and output the percentage of null values for each column C1 to C50, something like:

ColumnName NullPercentage
C1 14.28%

....
C50 6.89%

Thank you!

You can get the column names from system table SYSCOLUMNS.

You can use them to construct a dynamic SQL statement, and then use EXEC(@.string_variable) to run the query.

If you need further assistance, I can try to hunt up some code where I did this sort of thing to estimate the space consumed by a table. (Now with SQL Server 2005 you can find such space information, both for the table and its indexes, with "Properties" after right-clicking on the table name in SSMS.)

HTH.

Dan

|||I am fairy new to sql. If you could have some sample script that would be great! Thanks.
|||

I'll see if I can post the code tomorrow, Friday. Perhaps others here may beat me to it.

Dan

|||

Yes.. Here you go...

Hey Dan I never thought to beat you Buddy ..

Code Snippet

Declare @.Cols as Varchar(8000);

Declare @.PreparedCols as Varchar(8000);

Declare @.TableName as NVarchar(1000);

Select @.TableName = 'Orders'

Select

@.PreparedCols = ',Cast(Sum(Case When ? is null Then 1 Else 0 End)/Sum(1.0) * 100 as Numeric(5,2)) as [?]',

@.Cols = ''

Select

@.Cols = @.Cols + replace(@.PreparedCols, '?', name)

From

syscolumns

Where

id=Object_Id(@.TableName)

Exec ('Select ''NULL Value %'' as Comment' + @.Cols + ' From ' + @.TableName)

|||

Manivannen,

Not only did you beat me, but you provided a much-more compact solution!

I have never before built a dynamic SQL statement using a SELECT to append to an ever-growing SQL statement.

I like it!

Thanks for the education!

Where I tried it, though, I would like to note that I had to change @.COL to VARCHAR(MAX); it must have changed @.COL to NVARCHAR, since I could only print 4000 characters of it. It worked fine when I changed to VARCHAR(MAX). (Without the change the limit is around 40 columns, depending on the lengths of the column names.)

Dan

P.S. Is that enough exclamation points?

|||Beautiful!! Save me tons of time to check nulls.

I had to change to NVarchar(MAX) too, otherwise some of the tables will not work.

I marked both posts as answer.

Thanks!
|||

Thanks for your question, too!

I learn a lot by reading answers in this Forum.

Dan