mcclunyboy
Registered User.
- Local time
- Today, 15:01
- Joined
- Sep 8, 2009
- Messages
- 292
Hi,
I have a database I am working on, I need to compile a list of populated columns in each table. Some tables have too many columns to do it manually and I am hoping someone may know some SQL to help me. The DB is held in SQL Server 2005.
I have the following SQL which does part of the job, it returns all the columns which are not NULL however a lot of columns have 0.00 currency values as default so I would like to ignore these too.
I have a database I am working on, I need to compile a list of populated columns in each table. Some tables have too many columns to do it manually and I am hoping someone may know some SQL to help me. The DB is held in SQL Server 2005.
I have the following SQL which does part of the job, it returns all the columns which are not NULL however a lot of columns have 0.00 currency values as default so I would like to ignore these too.
Code:
[COLOR=blue][FONT="]declare[/FONT][/COLOR][FONT="] @col [COLOR=blue]varchar[/COLOR][COLOR=gray]([/COLOR]255[COLOR=gray]),[/COLOR] @cmd [COLOR=blue]varchar[/COLOR][COLOR=gray]([/COLOR][COLOR=fuchsia]max[/COLOR][COLOR=gray])[/COLOR][/FONT]
[COLOR=gray][FONT="] [/FONT][/COLOR]
[COLOR=blue][FONT="]DECLARE[/FONT][/COLOR][FONT="] getinfo [COLOR=blue]cursor[/COLOR] [COLOR=blue]for[/COLOR][/FONT]
[COLOR=blue][FONT="]SELECT[/FONT][/COLOR][FONT="] c[COLOR=gray].[/COLOR]name [COLOR=blue]FROM[/COLOR] [COLOR=green]sys.tables[/COLOR] t [COLOR=gray]JOIN[/COLOR] [COLOR=green]sys.columns[/COLOR] c [COLOR=blue]ON[/COLOR] t[COLOR=gray].[/COLOR][COLOR=fuchsia]Object_ID[/COLOR] [COLOR=gray]=[/COLOR] c[COLOR=gray].[/COLOR][COLOR=fuchsia]Object_ID[/COLOR][/FONT]
[COLOR=blue][FONT="]WHERE[/FONT][/COLOR][FONT="] t[COLOR=gray].[/COLOR]Name [COLOR=gray]=[/COLOR] [COLOR=red]'carl59_property'[/COLOR][/FONT]
[COLOR=red][FONT="] [/FONT][/COLOR]
[COLOR=blue][FONT="]OPEN[/FONT][/COLOR][FONT="] getinfo[/FONT]
[FONT="] [/FONT]
[COLOR=blue][FONT="]FETCH[/FONT][/COLOR][FONT="] [COLOR=blue]NEXT[/COLOR] [COLOR=blue]FROM[/COLOR] getinfo [COLOR=blue]into[/COLOR] @col[/FONT]
[FONT="] [/FONT]
[COLOR=blue][FONT="]WHILE[/FONT][/COLOR][FONT="] [COLOR=fuchsia]@@FETCH_STATUS[/COLOR] [COLOR=gray]=[/COLOR] 0[/FONT]
[COLOR=blue][FONT="]BEGIN[/FONT][/COLOR]
[FONT="] [COLOR=blue]SELECT[/COLOR] @cmd [COLOR=gray]=[/COLOR] [COLOR=red]'IF NOT EXISTS (SELECT top 1 * FROM carl59_property WHERE ['[/COLOR] [COLOR=gray]+[/COLOR] @col [COLOR=gray]+[/COLOR] [COLOR=red]'] IS NOT NULL) BEGIN print '''[/COLOR] [COLOR=gray]+[/COLOR] @col [COLOR=gray]+[/COLOR] [COLOR=red]''' end'[/COLOR][/FONT]
[FONT="] [COLOR=blue]EXEC[/COLOR][COLOR=gray]([/COLOR]@cmd[COLOR=gray])[/COLOR][/FONT]
[COLOR=gray][FONT="] [/FONT][/COLOR]
[FONT="] [COLOR=blue]FETCH[/COLOR] [COLOR=blue]NEXT[/COLOR] [COLOR=blue]FROM[/COLOR] getinfo [COLOR=blue]into[/COLOR] @col[/FONT]
[COLOR=blue][FONT="]END[/FONT][/COLOR]
[COLOR=blue][FONT="] [/FONT][/COLOR]
[COLOR=blue][FONT="]CLOSE[/FONT][/COLOR][FONT="] getinfo[/FONT]
[COLOR=blue][FONT="]DEALLOCATE[/FONT][/COLOR][FONT="] getinfo[/FONT][FONT="][/FONT]