SQL script for Column counts

Column_Counts.sql

DROP TABLE #Fields
CREATE TABLE #Fields(ColumnID int ,Field varchar(150) NOT NULL ,Records bigint NULL)

DECLARE @count INT
DECLARE @colCount INT
DECLARE @Column VARCHAR(150)
DECLARE @Table VarChar(150)
DECLARE @RecordCount BIGINT
DECLARE @Sql VarChar(8000)

Set NoCount On

Set @Table = 'PROFILE'

SET @count = 1
SELECT    @colCount = MAX(Ordinal_Position)
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE    TABLE_NAME = @Table

WHILE (@count < @colCount + 1)
BEGIN
  Select @Column = Column_Name 
  From   Information_Schema.Columns
  Where  Table_Name = @Table
         And Ordinal_Position = @Count

  Set    @SQL = '
           Insert
           Into    #Fields(ColumnId, Field, Records)
           Select ' + Convert(VarChar(10), @Count) + ',
                  ''' + @Column + ''',
                  Count(1) As Records
           From   docsadm.[' + @Table + ']
           Where  [' + @Column + '] Is Not NULL'

  Exec (@SQL)
  SET @count = @count + 1
END

SELECT * FROM #Fields