Not strictly Delphi, but useful for those that have to do diagnostics on SQL tables.

Not strictly Delphi, but useful for those that have to do diagnostics on SQL tables.

Originally shared by Lars Fosdal

Finding fields with a specific value in any table
(SQL Server)
Trying to find some mixed up data references, I wrote this little snippet to check for any field named "something" in any table, where the value ="value".

Example 1
Find rows with fields ending in 'id' and value equals 6653
exec [p_UTIL_FindFields]@Name= N'%id',@Value= N'6653' 
gives an output like
[dbo].[p_UTIL_FindFields]@Name= N'%id',@Value= N'6653'
Finding Fields with names like %id where value = 6653
select @countOut= count() from t_purchase_order_line_mvx_data where Id =  6653 returns 1
select @countOut= count() from t_purchase_order_tpack_reportings where Id =  6653 returns 1
select @countOut= count() from t_purchase_order_tpack_reportings where PurchaseOrderTPackLnkId =  6653 returns 2
select @countOut= count() from t_tpack_logs where Id =  6653 returns 1
select @countOut= count() from t_tpack_logs where TPackId =  6653 returns 4
select @countOut= count() from t_tpack_logs where DeliveryId =  6653 returns 14
select @countOut= count() from t_customer_order_delivery_shipping_packages where Id =  6653 returns 1
select @countOut= count() from t_customer_order_delivery_shipping_packages where CustomerOrderDeliveryId =  6653 returns 12
select @countOut= count() from t_tpack_move_orders where ToStoragePositionId =  6653 returns 1
select @countOut= count() from t_tpack_move_orders where OriginalToStoragePositionId =  6653 returns 1
select @countOut= count() from t_lot_tpack_logs where Id =  6653 returns 1
select @countOut= count() from t_lot_tpack_logs where LotTPackId =  6653 returns 1
select @countOut= count() from t_lot_tpack_logs where LotId =  6653 returns 8
select @countOut= count() from t_lot_tpack_logs where TPackId =  6653 returns 2
select @countOut= count() from t_lot_logs where LotId =  6653 returns 7
select @countOut= count() from t_tpack_purchaseorder_imports where Id =  6653 returns 1
select @countOut= count() from t_tpacks_to_production where Id =  6653 returns 1
Checked 435 table and column combos with 7 errors

Example 2
Find rows with fields containing 'storage' in their name, and value equals 6553
exec [p_UTIL_FindFields]@Name= N'%storage%',@Value= N'6653'
gives an output like
[p_UTIL_FindFields]@Name= N'%storage%',@Value= N'6653'
Finding Fields with names like %storage% where value = 6653
select @countOut= count() from t_tpack_move_orders where ToStoragePositionId =  6653 returns 1
select @countOut= count(*) from t_tpack_move_orders where OriginalToStoragePositionId =  6653 returns 1
Checked 64 table and column combos with 3 errors
http://pastebin.com/VujT0iGt