Find and return items based on matching strings across 17 tables
Is the application going to search for these one by one?  Is the performance of this query important?  Easy (ish) solution would be to create a stored procedure that accepted some arguments and then returned the results from searching each table.  

Results from the stored procedure could include the table it found a match on, the primary key and the field that matched.  Then the caller of that stored procedure could do more work based on that result set.  

EDIT - Below is a very basic sample procedure for you. 

CREATE PROCEDURE dbo.sp_FindCircuit
    (
    @Search NVARCHAR(100)
    )
AS
BEGIN

--Hold Results.
DECLARE @Results TABLE
    (
    TableName VARCHAR(100) NULL
    , FieldName VARCHAR(100) NULL
    , KeyValue INT NULL
    )

INSERT INTO @Results
        (TableName, FieldName, KeyValue)
SELECT 'Table 5'
    , 'Circuit ID'
    , &lt;PrimaryKey&gt;
FROM [Table 5]
WHERE CircuitID LIKE ('%' + @Search + '%')

--Repeat for each table.


--Return data.
SELECT TableName, FieldName, KeyValue
FROM @Results 

END