Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-01-19 : 05:29:04
|
| I want to create a copy of a (set of) existing tables, but I know that some of the columns in these tables are not used (i.e. all the entries in that column are null).Does anyone have any ideas on how I might be able to work out which columns are populated. I was wondering about some sort of aggregate for each column but am not sure if/how it would workthanks in advancesteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-19 : 07:31:43
|
| Something like:SELECT count(col1) col1, count(col2) col2, ...etc... , count(*) rows FROM myTableIf you get a zero for a column, you know that it contains nothing but nulls. You can compare the other counts to the row count to see which columns are sparsely populated with data. |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-19 : 10:06:28
|
| That could well do it. Many thankssteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-20 : 01:19:33
|
| I've got some code that will produce "stats" for all columns / all tables (or some variation therefore). This includes nmber fo rows used, whether all (varchar) numeric/date, how many unique values a column contains, top 10 most frequently used values, etc. This is intended to be dropped into Excel for analysis - any good to you?Its not really code I can post here, but I'd be happy to email it to you.Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-20 : 03:28:12
|
| Kristen - That would be great, thanks very muche-beer tokens in the post :)steveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-20 : 08:05:40
|
| YHM |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-01-21 : 18:27:53
|
| yhm? Many thanks for the script Kristen, it's greatly appreciated. May your reward be in heaven (or failing that - before you get there and preferably in a pub)steveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
|
|
|
epanther
Starting Member
8 Posts |
Posted - 2005-01-21 : 23:58:43
|
| YHM = You Have MailPanther |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-22 : 02:15:52
|
| "preferably in a pub"Cheers! |
|
|
|
|
|
|
|
|
Waste not want not