SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE lkpTable (
  "TableName" sysname,
  "requiredcolumns" nvarchar(max)
);

INSERT INTO lkpTable
  ("TableName", "requiredcolumns")
VALUES
  ('Pricedetails', 'sellingPrice,RetailPrice,Wholesaleprice'),
  ('Itemdetails', 'ItemID,Itemprice');

CREATE TABLE Pricedetails (
  "Priceid" INTEGER,
  "Mafdate" VARCHAR(10),
  "MafName" VARCHAR(8),
  "sellingPrice" FLOAT,
  "RetailPrice" FLOAT,
  "Wholesaleprice" FLOAT
);

INSERT INTO Pricedetails
  ("Priceid", "Mafdate", "MafName", "sellingPrice", "RetailPrice", "Wholesaleprice")
VALUES
  ('01', '2020-01-01', 'Americas', '25.00', '43.33', '33.66'),
  ('02', '2020-01-01', 'Americas', '43.45', '22.55', '11.11'),
  ('03', '2021-01-01', 'Asia', '-23.00', '-34.00', '23.00');

CREATE TABLE Itemdetails (
  "ItemID" INTEGER,
  "ItemPrice" FLOAT,
  "Itemlocation" VARCHAR(8),
  "ItemManuf" VARCHAR(2)
);

INSERT INTO Itemdetails
  ("ItemID", "ItemPrice", "Itemlocation", "ItemManuf")
VALUES
  ('01', '45.11', 'Americas', 'SA'),
  ('02', '25.00', 'Americas', 'SA'),
  ('03', '35.67', 'Americas', 'SA');
8 rows affected

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG('
SELECT
  TableName = ' + QUOTENAME(t.name, '''') + ',
  ColumnName,
  IsInt
FROM (
    SELECT ' + c.BeforePivotCoumns + '
    FROM ' + QUOTENAME(t.name) + '
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    ' + c.UnpivotColumns + '
  )
) u
', '
UNION ALL '
  )
FROM sys.tables t
JOIN lkpTable lkp ON lkp.TableName = t.name
CROSS APPLY (
    SELECT
      BeforePivotCoumns = STRING_AGG(CAST('
    ' + QUOTENAME(c.name) + ' = CASE WHEN COUNT(CASE WHEN ROUND(' + QUOTENAME(c.name) + ', 0) <> ' + QUOTENAME(c.name) + ' THEN 1 END) = 0 THEN ''All ints'' ELSE ''Not All ints'' END'
        AS nvarchar(max)), ','),
      
      UnpivotColumns = STRING_AGG(QUOTENAME(c.name), ', ')
      
    FROM sys.columns c
    JOIN STRING_SPLIT(lkp.requiredcolumns, ',') req ON req.value = c.name
    WHERE c.object_id = t.object_id
) c;

PRINT @sql;

EXEC sp_executesql @sql;


TableName
ColumnName
IsInt


Pricedetails
Not All ints
sellingPrice

Pricedetails
Not All ints
RetailPrice

Pricedetails
Not All ints
Wholesaleprice

Itemdetails
All ints
ItemID

Itemdetails
Not All ints
ItemPrice



SELECT
  TableName = 'Pricedetails',
  ColumnName,
  IsInt
FROM (
    SELECT 
    [sellingPrice] = CASE WHEN COUNT(CASE WHEN ROUND([sellingPrice], 0) <> [sellingPrice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [RetailPrice] = CASE WHEN COUNT(CASE WHEN ROUND([RetailPrice], 0) <> [RetailPrice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [Wholesaleprice] = CASE WHEN COUNT(CASE WHEN ROUND([Wholesaleprice], 0) <> [Wholesaleprice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END
    FROM [Pricedetails]
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    [sellingPrice], [RetailPrice], [Wholesaleprice]
  )
) u

UNION ALL 
SELECT
  TableName = 'Itemdetails',
  ColumnName,
  IsInt
FROM (
    SELECT 
    [ItemID] = CASE WHEN COUNT(CASE WHEN ROUND([ItemID], 0) <> [ItemID] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [ItemPrice] = CASE WHEN COUNT(CASE WHEN ROUND([ItemPrice], 0) <> [ItemPrice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END
    FROM [Itemdetails]
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    [ItemID], [ItemPrice]
  )
) u

Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @unionall nvarchar(100) = '
UNION ALL ';

DECLARE @sql nvarchar(max);

SET @sql = STUFF(
  (SELECT @unionall + '
SELECT
  TableName = ' + QUOTENAME(t.name, '''') + ',
  ColumnName,
  IsInt
FROM (
    SELECT ' + STUFF(c1.BeforePivotCoumns.value('text()[1]','nvarchar(max)'), 1, 1, '') + '
    FROM ' + QUOTENAME(t.name) + '
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
    ' + STUFF(c2.UnpivotColumns.value('text()[1]','nvarchar(max)'), 1, 1, '') + '
  )
) u'
FROM sys.tables t
JOIN (
    SELECT DISTINCT
      lkp.TableName
    FROM lkpTable lkp
) lkp ON lkp.TableName = t.name

CROSS APPLY (
    SELECT
      ',
    ' + QUOTENAME(c.name) + ' = CASE WHEN COUNT(CASE WHEN ROUND(' + QUOTENAME(c.name) + ', 0) <> ' + QUOTENAME(c.name) + ' THEN 1 END) = 0 THEN ''All ints'' ELSE ''Not All ints'' END'
    FROM lkpTable lkp2
    CROSS APPLY STRING_SPLIT(lkp2.requiredcolumns, ',') req
    JOIN sys.columns c ON req.value = c.name
    WHERE c.object_id = t.object_id
      AND lkp2.TableName = lkp.TableName
    FOR XML PATH(''), TYPE
) c1(BeforePivotCoumns)

CROSS APPLY (
    SELECT
       ', ' + QUOTENAME(c.name)
    FROM lkpTable lkp2
    CROSS APPLY STRING_SPLIT(lkp2.requiredcolumns, ',') req
    JOIN sys.columns c ON req.value = c.name
    WHERE c.object_id = t.object_id
      AND lkp2.TableName = lkp.TableName
    FOR XML PATH(''), TYPE
) c2(UnpivotColumns)

FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)'), 1, LEN(@unionall), '');


PRINT @sql;

EXEC sp_executesql @sql;


TableName
ColumnName
IsInt


Itemdetails
All ints
ItemID

Itemdetails
Not All ints
ItemPrice

Pricedetails
Not All ints
sellingPrice

Pricedetails
Not All ints
RetailPrice

Pricedetails
Not All ints
Wholesaleprice


 
SELECT
  TableName = 'Itemdetails',
  ColumnName,
  IsInt
FROM (
    SELECT 
    [ItemID] = CASE WHEN COUNT(CASE WHEN ROUND([ItemID], 0) <> [ItemID] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [ItemPrice] = CASE WHEN COUNT(CASE WHEN ROUND([ItemPrice], 0) <> [ItemPrice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END
    FROM [Itemdetails]
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
     [ItemID], [ItemPrice]
  )
) u
UNION ALL 
SELECT
  TableName = 'Pricedetails',
  ColumnName,
  IsInt
FROM (
    SELECT 
    [sellingPrice] = CASE WHEN COUNT(CASE WHEN ROUND([sellingPrice], 0) <> [sellingPrice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [RetailPrice] = CASE WHEN COUNT(CASE WHEN ROUND([RetailPrice], 0) <> [RetailPrice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END,
    [Wholesaleprice] = CASE WHEN COUNT(CASE WHEN ROUND([Wholesaleprice], 0) <> [Wholesaleprice] THEN 1 END) = 0 THEN 'All ints' ELSE 'Not All ints' END
    FROM [Pricedetails]
) t
UNPIVOT (
  ColumnName FOR IsInt IN (
     [sellingPrice], [RetailPrice], [Wholesaleprice]
  )
) u
Warning: Null value is eliminated by an aggregate or other SET operation.
TableName	ColumnName	IsInt
Pricedetails	Not All ints	sellingPrice
Pricedetails	Not All ints	RetailPrice
Pricedetails	Not All ints	Wholesaleprice
Itemdetails	All ints	ItemID
Itemdetails	Not All ints	ItemPrice