Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL script for Column counts
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment