User1203305613 posted
We have a tool which creates UI for us , for now in some cases when we select Date field , it creates columns as datetime in sql server , but inserts zeroes in time part.
I wrote a script like the below one to identify such columns, yes we need to fix rootcause but in the interim we need to identify such columns and alter them to date.
The below script is too slow , want to know if there could be a better one.
DECLARE @table_variable TABLE (
tblecol varchar(max)
);
DECLARE @tempcount int
declare @tname varchar(100),@colname varchar(100)
DECLARE @tCOLNAME VARCHAR(MAX)
declare @sql NVARCHAR(MAX);
DECLARE @RESULT INT
DECLARE cursor_product CURSOR
FOR
SELECT table_name,column_name
FROM INFORMATION_SCHEMA.COLUMNS (noLOCK)
where data_type ='datetime'
and column_name not in('CreatedOn', 'LastModifiedOn')
OPEN cursor_product;
FETCH NEXT FROM cursor_product INTO
@tname,
@colname;
WHILE @@FETCH_STATUS = 0
BEGIN
set @tCOLNAME= CONCAT(@tname,'.',@colname)
SET @sql = N' select @ROWNUMBER= coalesce(COUNT( distinct cast( ' + @tCOLNAME +' as time)),0) from '+ @tname;
--print @sql
EXEC sp_executesql @sql,N'@RowNumber INT OUTPUT',@RowNumber=@Result OUTPUT;
set @tempcount=0
SELECT @tempcount= @Result
if(@tempcount=1)
begin
INSERT INTO @table_variable values (@tCOLNAME)
end
FETCH NEXT FROM cursor_product INTO
@tname,
@colname;
END;
CLOSE cursor_product;
DEALLOCATE cursor_product;
select * from @table_variable
