declare @SqlString varchar(8000),
		@Column_name varchar(150),
		@Counter numeric,
		@Max numeric,
	    @Total numeric

--Create Audit Table if not exists
If Not Exists (Select NAME From Sysobjects WHERE NAME = 'Table_Audit')
Begin
	Create Table Table_Audit
	(
		Table_Name varchar(200),
		Total_Count numeric,
		Column_Name varchar(200), 
		Blank_Quantity int, 
		Percentage_Blank float, 
		Distinct_Count int, 
		Percentage_Distinct float,
		Min_Length int, 
		Max_Length int, 
		Min_value nvarchar(400), 
		Max_Value nvarchar(400), 
		Nulls int,
		Date_run datetime
	)
end

--truncate table...
truncate table Table_Audit

set @Total = (select count(*) from Supplier_Table)
set @Max = (select count(*) from information_schema.columns where table_name = 'Supplier_Table')

set @Counter = 1

while @Counter <= @Max
	Begin
		set @Column_name = (select Column_name from information_schema.columns where table_name = 'Supplier_Table' and Ordinal_position = @Counter)
		set @Sqlstring = '
		select ''Supplier_Table''as Table_Name, 
		count(*) as Total_Count,
		''' +@Column_Name+ '''as Column_Name, 
		sum(case when cast([' +@Column_Name+ '] as varchar) is not null and cast([' +@Column_Name+ '] as varchar) <> '''' then 0 else 1 end) as Blank,
		left(cast(sum(case when cast([' +@Column_Name+ '] as varchar) is not null and cast([' +@Column_Name+ '] as varchar) <> '''' then 0 else 1 end) as numeric)
		/ ' + cast(@Total as varchar) +' * 100,5) as Percentage_Blank,
		count(distinct [' +@Column_Name+ ']) as [Distinct],
		left(cast(count(distinct [' +@Column_Name+ ']) as numeric)/' + cast(@Total as varchar) +' * 100,5) as Percentage_Distinct,
		min(len(cast([' +@Column_Name+ '] as varchar))) as Min_length, 
		max(len(cast([' +@Column_Name+ '] as varchar))) as Max_Length,
		min([' +@Column_Name+ ']) as Min_Value,
		max([' +@Column_Name+ ']) as Max_Value,
     	sum(case when [' +@Column_Name+ '] is null then 1 else 0 end) as [Nulls],
		getdate() as Date_Run
		from Supplier_Table'
		--print @Sqlstring
		insert into Table_Audit
		exec (@Sqlstring)
		set @Counter = @Counter + 1
	End