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
