CountNonNullRows.ps1

# needed to count number of rows populated in filenet database docversion and generic tables for a filenet migration analysis
# wasn't able to use sql server client tools so had to do this in powershell...

function Invoke-SQL {
    param(
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Server = someServer,4000; Network Library=DBMSSOCN; Initial catalog = somedb; User Id = someUser; Password = somePassword; Connection Timeout = 120"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $command.CommandTimeout = 120
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables

}

$sql = "
declare @name varchar(50)
declare @sql varchar(200)

declare dbc cursor for 
    select column_name from information_schema.columns where table_name = 'generic'
open dbc
print 'here'
fetch next from dbc into @name
while @@fetch_status = 0
begin

    set @sql = 'select ''' + @name + ''' as [Column], count(*) as [NullCount], (select count(*) from docversion) as [RowCount]  from generic where ' + @name + ' is null'
    exec (@sql)

    fetch next from dbc into @name
end
close dbc
deallocate dbc
"

Invoke-SQL  $sql