Rich Benner Rich Benner - 5 months ago 26
SQL Question

SQL Cursor to use Table and Field Names from Temp Table

I'll preface this by letting you all know that I promised myself a few years ago never to use a cursor in SQL where it's not needed. Unfortunately I think I may have to use one in my current situation but it's been so long that I'm struggling to remember the correct syntax.

Basically, I've got a problem with

CONVERT_IMPLICIT
happening in queries because I have data types that are different for the same field in different tables so I'd like to eventually convert these to
int
. But to do this I need to check whether all data can be converted to int or not to see how big the job is.

I've got the query below which gives me a list of all tables in the database that contain the relevant field in a list;

IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData
GO
CREATE TABLE #BaseData (Table_Name varchar(100), Field_Name varchar(100), Data_Type_Desc varchar(20), Data_Max_Length int, Convertible bit)

DECLARE @FieldName varchar(20); SET @FieldName = 'TestFieldName'

INSERT INTO #BaseData (Table_Name, Field_Name, Data_Type_Desc, Data_Max_Length)
SELECT
o.name ,c.name ,t.name ,t.max_length
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
JOIN sys.objects o
ON c.object_id = o.object_id
WHERE c.name LIKE '%' + @FieldName + '%'
AND o.type_desc = 'USER_TABLE'


Which gives results like this;

Table_Name Field_Name Data_Type_Desc Data_Max_Length Convertible
Table1 TestFieldName varchar 8000 NULL
Table2 TestFieldName nvarchar 8000 NULL
Table3 TestFieldName int 4 NULL
Table4 TestFieldName varchar 8000 NULL
Table5 TestFieldName varchar 8000 NULL


What I'd like to do is to check if all data in the relevant table & field can be converted to an int and update the 'convertible' field (1 if there's data that can't be converted, 0 if the data is fine). I've got the following calculation which works perfectly fine;

'SELECT
CASE
WHEN COUNT(' + @FieldName + ') - SUM(ISNUMERIC(' + @FieldName + ')) > 0
THEN 1
ELSE 0
END
FROM ' + @TableName


And gives the result that I'm after. But I'm struggling to get to the correct syntax to create the cursor which will look at each row in my temp table and run this SQL accordingly. It then needs to update the final column of the temp table with the result of the query (1 or 0).

This will have to be run on a couple of hundred databases which is why I need this list to be dynamic, there may well be custom tables in some databases (in fact, it's pretty likely).

If anybody could give any guidance it would be greatly appreciated.

Thanks

SMM SMM
Answer

I made a couple of changes to your original query but here is something that should work. I have done similar things in the past :-)

Changes:

  • Added schema to the source table - my test database had matches in multiple schemas
  • Changed datatypes to sysname, smallint to match table definitions or names could get truncated

    IF OBJECT_ID('tempdb..#BaseData') IS NOT NULL DROP TABLE #BaseData;
    GO
    
    CREATE TABLE #BaseData (Schema_Name sysname, Table_Name sysname, Field_Name sysname, Data_Type_Desc sysname, Data_Max_Length smallint, Convertible bit);
    
    DECLARE @FieldName varchar(20); SET @FieldName = 'TestFieldName';
    
    INSERT INTO #BaseData (Schema_Name, Table_Name, Field_Name, Data_Type_Desc, Data_Max_Length)
    SELECT
    s.name, o.name ,c.name ,t.name ,t.max_length 
    FROM sys.columns c
    JOIN sys.types t
        ON c.user_type_id = t.user_type_id
    JOIN sys.objects o
        ON c.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id=s.schema_id
    WHERE c.name LIKE '%' + @FieldName + '%'
        AND o.type_desc = 'USER_TABLE';
    
    --select * from #BaseData;
    
    DECLARE @sName sysname,
            @tName sysname,
            @fName sysname,
            @sql VARCHAR(MAX);
    
    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
        SELECT  Schema_Name,
                Table_Name,
                Field_Name
        FROM #BaseData;
    OPEN c;
    FETCH NEXT FROM c INTO @sName, @tName, @fName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'UPDATE #BaseData SET Convertible =   
                    (SELECT 
                    CASE 
                        WHEN COUNT(' + @fName + ') - SUM(ISNUMERIC(' + @fName + ')) > 0 
                            THEN 1 
                        ELSE 0
                    END Convertible
                    FROM ' + @sName + '.' + @tName + ')
                FROM #BaseData WHERE Schema_Name = ''' + @sName + ''' AND Table_Name = ''' + @tName + ''' AND Field_Name = ''' + @fName + '''';
    
    --select @sql;
    EXEC(@sql); 
    
    FETCH NEXT FROM c INTO @sName, @tName, @fName;
    END
    
    CLOSE c;
    DEALLOCATE c;
    
    select *
    from #BaseData;