Циклический просмотр нескольких столбцов с плавающей точкой в ​​БД с использованием динамического SQL

use BRSDATA;

begin try drop table #temp end try begin catch end catch ;

DECLARE @TableName nvarchar(250); 
DECLARE @ColumnName nvarchar(250); 

declare @x int;

DECLARE @FLOATV as float = 3.141592653;--searching for this in all float vals in all tables

SELECT t.name,c.name colname 
    into #temp
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  inner join sys.columns AS c
  on c.object_id = t.object_id
  WHERE s.name = 'dbo'
  and type_desc = 'USER_TABLE'
  and exists(select * from  sys.types where name = 'float' and system_type_id = c.system_type_id);

  SET Nocount on;

  DECLARE FLOAT_TAB CURSOR FOR
    SELECT DISTINCT [name] from #temp;
        OPEN FLOAT_TAB;
        FETCH NEXT FROM FLOAT_TAB
        INTO @TableName;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @SQL nvarchar(max) = '';
            DECLARE @COND nvarchar(max) = '';
            SET  @SQL = 'SELECT ';

             DECLARE COL_TAB CURSOR FOR
                SELECT colname from #temp where name = @TableName;
                    OPEN COL_TAB;
                    FETCH NEXT FROM COL_TAB
                        INTO @ColumnName;

                WHILE @@FETCH_STATUS = 0
                BEGIN

                    SET @SQL = @SQL + ' ' + @ColumnName + ',';
                    SET @COND = @COND + @ColumnName + ' = ' + cast(@floatv as nvarchar(60)) + ' OR ' ;
                    FETCH NEXT FROM col_tab INTO @ColumnName

                END

                SET @SQL = LEFT(@SQL, len(@sql) - 1) + ' FROM ' + @TableName +  ' WHERE ' + LEFT(@COND, LEN(@COND) - 3);
                print @sql;
                EXECUTE ( @SQL);
                close col_tab;
                deallocate col_tab;
                --waitfor  delay '00:05';
            FETCH NEXT FROM FLOAT_TAB into @tablename;
        END
        close float_tab
        deallocate float_tab;

Я считаю, что этот сценарий может помочь вам:

DECLARE @Query TABLE (SchemaName    VARCHAR(100)
                    ,TableName  VARCHAR(100)
                    ,ColumnName VARCHAR(1000)
                    )
INSERT INTO @Query
SELECT A.TABLE_SCHEMA,A.TABLE_NAME,' MAX(CASE Charindex(''.'',' + QUOTENAME(COLUMN_NAME) + ') 
                                        WHEN 0 THEN 0 
                                        ELSE Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + QUOTENAME(COLUMN_NAME) + ', 128) ) AS FLOAT) AS BIGINT)) 
                                        END) AS ['+COLUMN_NAME+'_MAX_LENGTH]'
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.TABLES B
ON A.TABLE_NAME = B.TABLE_NAME
WHERE TABLE_TYPE = 'BASE TABLE'
AND DATA_TYPE = 'FLOAT'

SELECT 'SELECT '+
        STUFF((
            SELECT ',' + ColumnName
            FROM @Query Q2
            WHERE Q1.SchemaName=q2.SchemaName AND  Q1.TableName=q2.TableName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
            , 1, 2, '')
            +' FROM '+Q1.SchemaName+'.'+Q1.TableName
FROM 
(SELECT DISTINCT SchemaName,TableName FROM @Query) Q1

не уверен, что это то, что тебе нужно, но вот как бы я это сделал:

   DECLARE @cols NVARCHAR(MAX)
    ,@table NVARCHAR(MAX)
    ,@sql NVARCHAR(MAX)
    ,@X INT = 1


WHILE @X < (
    SELECT MAX(ROWID) FROM 
    (
        SELECT quotename(COLUMN_NAME) col
        ,ROW_NUMBER()over(ORDER BY COLUMN_NAME) ROWID
        FROM INFORMATION_SCHEMA.COLUMNS A
        inner join INFORMATION_SCHEMA.TABLES B
        on A.TABLE_NAME = B.TABLE_NAME
        where TABLE_TYPE = 'BASE TABLE'
        and DATA_TYPE = 'Float'
    ) AS X
  WHERE ROWID < 10
)
 BEGIN

SET @cols = 
(SELECT col FROM 
(
    SELECT quotename(COLUMN_NAME) col
    ,ROW_NUMBER()over(ORDER BY COLUMN_NAME) ROWID
    FROM INFORMATION_SCHEMA.COLUMNS A
    inner join INFORMATION_SCHEMA.TABLES B
    on A.TABLE_NAME = B.TABLE_NAME
    where TABLE_TYPE = 'BASE TABLE'
    and DATA_TYPE = 'Float'
) AS X
WHERE ROWID = @X 
)

SET @table = 
(SELECT tablename FROM 
(
    SELECT quotename(A.TABLE_NAME) tablename
    ,ROW_NUMBER()over(ORDER BY COLUMN_NAME) ROWID
    FROM INFORMATION_SCHEMA.COLUMNS A
    inner join INFORMATION_SCHEMA.TABLES B
    on A.TABLE_NAME = B.TABLE_NAME
    where TABLE_TYPE = 'BASE TABLE'
    and DATA_TYPE = 'Float'
) AS X
WHERE ROWID = @X 
)


Set @sql =  'SELECT MAX(CASE Charindex(''.'',' + @cols + ')
                    WHEN 0 THEN 0
                    ELSE
        Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + @cols + ', 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH
        ,MAX(CASE Charindex(''.'',' + @table + ')
                    WHEN 0 THEN 0
                    ELSE
        Len (Cast(Cast(Reverse(CONVERT(VARCHAR(50), ' + @table + ', 128)
                     ) AS FLOAT) AS BIGINT))
                  END) AS MAX_LENGTH'

PRINT(@sql)
SET @X = @X +1
END