SQL Cursor to use Table and Field Names from Temp Table
<p>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.</p>

<p>Basically, I've got a problem with <code>CONVERT_IMPLICIT</code> 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 <code>int</code>. 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.</p>

<p>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;</p>

<pre><code>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'
</code></pre>

<p>Which gives results like this;</p>

<pre><code>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
</code></pre>

<p>What I'd like to do is to check if all data in the relevant table &amp; 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;</p>

<pre><code>'SELECT  
CASE 
    WHEN COUNT(' + @FieldName + ') - SUM(ISNUMERIC(' + @FieldName + ')) &gt; 0 
        THEN 1 
    ELSE 0 
END 
FROM ' + @TableName
</code></pre>

<p>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).</p>

<p>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).</p>

<p>If anybody could give any guidance it would be greatly appreciated.</p>

<p>Thanks</p>