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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.