SQL to return columns with values

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.

Code:
  [COLOR=blue][FONT=&quot]declare[/FONT][/COLOR][FONT=&quot] @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=&quot] [/FONT][/COLOR]
   [COLOR=blue][FONT=&quot]DECLARE[/FONT][/COLOR][FONT=&quot] getinfo [COLOR=blue]cursor[/COLOR] [COLOR=blue]for[/COLOR][/FONT]
   [COLOR=blue][FONT=&quot]SELECT[/FONT][/COLOR][FONT=&quot] 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=&quot]WHERE[/FONT][/COLOR][FONT=&quot] t[COLOR=gray].[/COLOR]Name [COLOR=gray]=[/COLOR] [COLOR=red]'carl59_property'[/COLOR][/FONT]
   [COLOR=red][FONT=&quot] [/FONT][/COLOR]
   [COLOR=blue][FONT=&quot]OPEN[/FONT][/COLOR][FONT=&quot] getinfo[/FONT]
   [FONT=&quot] [/FONT]
   [COLOR=blue][FONT=&quot]FETCH[/FONT][/COLOR][FONT=&quot] [COLOR=blue]NEXT[/COLOR] [COLOR=blue]FROM[/COLOR] getinfo [COLOR=blue]into[/COLOR] @col[/FONT]
   [FONT=&quot] [/FONT]
   [COLOR=blue][FONT=&quot]WHILE[/FONT][/COLOR][FONT=&quot] [COLOR=fuchsia]@@FETCH_STATUS[/COLOR] [COLOR=gray]=[/COLOR] 0[/FONT]
   [COLOR=blue][FONT=&quot]BEGIN[/FONT][/COLOR]
   [FONT=&quot]    [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=&quot]    [COLOR=blue]EXEC[/COLOR][COLOR=gray]([/COLOR]@cmd[COLOR=gray])[/COLOR][/FONT]
   [COLOR=gray][FONT=&quot] [/FONT][/COLOR]
   [FONT=&quot]    [COLOR=blue]FETCH[/COLOR] [COLOR=blue]NEXT[/COLOR] [COLOR=blue]FROM[/COLOR] getinfo [COLOR=blue]into[/COLOR] @col[/FONT]
   [COLOR=blue][FONT=&quot]END[/FONT][/COLOR]
   [COLOR=blue][FONT=&quot] [/FONT][/COLOR]
   [COLOR=blue][FONT=&quot]CLOSE[/FONT][/COLOR][FONT=&quot] getinfo[/FONT]
   [COLOR=blue][FONT=&quot]DEALLOCATE[/FONT][/COLOR][FONT=&quot] getinfo[/FONT][FONT=&quot][/FONT]
 
ok I have it sorted - sql below.

New problem is this doesn't account for date/time fields....bah...I am not going to spend anymore time on it as it probably isn't worth the hassle. However if someone knows a quicker solution than all this please let me know.

Don't know how I missed it, simply the tiny and statement fixed it.

Code:
declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'carl59_bl'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM carl59_bl WHERE [' + @col + '] IS NOT NULL and [' + @col + '] != ''0'') BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo
 

Users who are viewing this thread

Back
Top Bottom