Pat on the back for who can help solve this debugging...
-
Hi If anybody can help me solve this problem asap, then I would appreciate it very much!!
Bascically the SP is designed to audit a table, i.e blank field counts, min, max counts etc...
--exec SP_AUDIT_TABLE 'Supplier_Table'
ALTER PROC SP_AUDIT_TABLE
@Table_Name varchar(150)
as
declare @SqlString nvarchar(4000),
@Column_name varchar(150),
@Counter numeric,
@Total int,
@max-2 int
--Create Audit Table if not exists
If Not Exists (Select NAME From Sysobjects WHERE NAME = 'Table_Audit')
Begin
Create Table Table_Audit
(
Table_Name nvarchar(200),
Total_Count int,
Column_Name nvarchar(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 @SqlString = N'select @Total = count(*) from ' + @Table_Name
Exec sp_executesql @SqlString, N'@Total int output', @Total Output
set @SqlString = N'select @max-2 = count(*) from information_schema.columns where table_name = ''' + @Table_Name + ''''
Exec sp_executesql @SqlString, N'@Max int output', @max-2 Output
set @Counter = 1
while @Counter <= @max-2
Begin
set @SqlString = N'select @Column_name = Column_Name from information_schema.columns where table_name = ''' + @Table_Name + ''' and Ordinal_position = ' +@Counter
Exec sp_executesql @SqlString, N'@Column_name varchar(150) output', @Column_name Output
set @Sqlstring = '
select '''+@Table_Name+'''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,
cast(min([' +@Column_Name+ ']) as varchar) as Min_Value,
cast(max([' +@Column_Name+ ']) as varchar) as Max_Value,
sum(case when [' +@Column_Name+ '] is null then 1 else 0 end) as [Nulls],
getdate() as Date_Run
from ' + @Table_Name
--print @Sqlstring
insert into Table_Audit
exec sp_executesql @Sqlstring
set @Counter = @Counter + 1
End
select * from Table_Audit
Msg 8115, Level 16, State 6, Procedure SP_AUDIT_TABLE, Line 46
Arithmetic overflow error converting nvarchar to data type numeric.
Msg 137, Level 15, State 1, Line 1
Must declare the variable '@Max'.
(0 row(s) affected)
Msg 8115, Level 16, State 6, Procedure SP_AUDIT_TABLE, Line 46
Arithmetic overflow error converting nvarchar to data type numeric.
(0 row(s) affected)
Msg 8115, Level 16, State 6, Procedure SP_AUDIT_TABLE, Line 46
Arithmetic overflow error converting nvarchar to data type numeric.
(0 row(s) affected)
Msg 8115, Level 16, State 6, Procedure SP_AUDIT_TABLE, Line 46
Arithmetic overflow error converting nvarchar to data type numeric.
Etc…..
-
No idea whether its a factor but @Tablename is Varchar and you're embedding it in an nVarChar string. Not sure what that does.
-
set @SqlString = N'select @Column_name = Column_Name from information_schema.columns where table_name = ''' + @Table_Name + ''' and Ordinal_position = ' + @Counter
This set statement is intended to append the value of @Counter to @SqlString but @Counter is numeric.
To correct, cast @Counter to a character datatype:
set @SqlString = N'select @Column_name = Column_Name from information_schema.columns where table_name = ''' + @Table_Name + ''' and Ordinal_position = '
--
+ CAST( @Counter as varchar(20) )
SQL = Scarcely Qualifies as a Language
-
I would also initialize my variables before using them - it's just good programming practice:
set @Total = 0
set @max-2 = 0
-
Cheers I managed to get it working by changing the table nvarchar types to varchar.
Quite a useful SP if you need to audit a table.
FYI code i used:
CREATE PROC SP_AUDIT_TABLE
@Table_Name nvarchar(150)
as
declare @SqlString nvarchar(4000),
@Column_name nvarchar(500),
@Counter int,
@Total int,
@max-2 int
--Create Audit Table if not exists
If Not Exists (Select NAME From Sysobjects WHERE NAME = 'Audit_Table')
Begin
Create Table Audit_Table
(
Table_Name varchar(500),
Total_Count int,
Column_Name varchar(500),
Blank_Quantity int,
Percentage_Blank float,
Distinct_Count int,
Percentage_Distinct float,
Min_Length int,
Max_Length int,
Min_value varchar(500),
Max_Value varchar(500),
Nulls int,
Date_run datetime
)
end
--truncate table
truncate table Audit_Table
--set @Total
set @SqlString = N'select @Total = count(*) from ' + @Table_Name
Exec sp_executesql @SqlString, N'@Total int output', @Total Output
--set @max-2
set @SqlString = N'select @max-2 = count(*) from information_schema.columns where table_name = ''' + @Table_Name + ''''
Exec sp_executesql @SqlString, N'@Max int output', @max-2 Output
set @Counter = 1
while @Counter <= @max-2
Begin
select @Column_name = Column_Name from information_schema.columns where table_name = @Table_Name and Ordinal_position = @Counter
set @Sqlstring = '
select '''+@Table_Name+'''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,
cast(min([' +@Column_Name+ ']) as varchar) as Min_Value,
cast(max([' +@Column_Name+ ']) as varchar) as Max_Value,
sum(case when [' +@Column_Name+ '] is null then 1 else 0 end) as [Nulls],
getdate() as Date_Run
from ' + @Table_Name
insert into Audit_Table
exec sp_executesql @Sqlstring
set @Counter = @Counter + 1
End
Thanks!
Not fool proof and prone to ntext errors, but handy none the less....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply