I have almost 1000 tables and most of them have a common column ItemNumber. How do I search across all the tables in the database for a value or list of values that exist in this common column, such as 350 or (350, 465)? The tables have different schemas.

Table A100

Table A1000

CodePudding user response：

This does not perform type checking, so you can get conversion errors if the target column is not the correct type. Also, this script uses LIKE, you would probably need to change that to a direct comparison.

SET NOCOUNT ON

DECLARE @ID NVARCHAR(100) = '2'
DECLARE @ColumnName NVARCHAR(100) ='UserID'

DECLARE @Sql NVARCHAR(MAX)=N'CREATE TABLE #TempResults(TableName NVARCHAR(50), ColumnName NVARCHAR(50), ItemCount INT)'

SELECT      
     @Sql = @Sql   N'INSERT INTO #TempResults SELECT * FROM (SELECT ''' ST.Name ''' AS TableName,  ''' C.Name ''' AS ColumnName, COUNT(*) AS ItemCount FROM ' ST.Name ' WHERE ' C.Name '=' @ID ') AS X WHERE ItemCount > 0 '  
FROM        
    sys.columns C
    INNER JOIN sys.tables ST ON C.object_id = ST.object_id
WHERE      
    C.Name  LIKE '%' @ColumnName '%'

SET @Sql = @Sql   N'SELECT * FROM #TempResults'

exec sp_executesql @sql

CodePudding user response：

You need to do this with dynamic SQL. You will need to query all 1000 tables, and make sure you are converting the values correctly if the columsn are different types.

You don't need a temp table for this, you can just script one giant UNION ALL query. You must make sure to quote all dynamic names correctly using QUOTENAME.

To be able to return data for multiple items, you should create a Table Valued Parameter, which you can pass in using sp_executesql.

First create a table type

CREATE TYPE dbo.IntList (Id int PRIMARY KEY);

Then you create a table variable containing them, and pass it in. You can also do this in a client application and pass in a TVP.

SET NOCOUNT ON;

DECLARE @Items dbo.IntList;
INSERT @Items (Id) VALUES(350),(465);


DECLARE @Sql nvarchar(max);

SELECT      
     @Sql = STRING_AGG(CONVERT(nvarchar(max), N'
SELECT
  '   QUOTENAME(t.name, '''')   ' AS TableName,
  t.ItemNumber,
  COUNT(*) AS ItemCount
FROM '   QUOTENAME(t.Name)   ' t
JOIN @items i ON i.Id = t.ItemNumber
GROUP BY
  t.ItemNumber
HAVING COUNT(*) > 0
'   ),
    N'
UNION ALL
'   )

FROM        
    sys.tables t
WHERE t.object_id IN (
    SELECT c.object_id
    FROM sys.columns c
    WHERE      
      c.Name = 'ItemNumber'
);

PRINT @sql;  -- your friend

EXEC sp_executesql
  @sql,
  N'@items dbo.IntList',
  @items = @items READONLY;

If you don't need to know the count, and only want to know if a value exists, you can change the dynamic SQL to an EXISTS

....
SELECT      
     @Sql = STRING_AGG(CONVERT(nvarchar(max), N'
SELECT
  '   QUOTENAME(t.name, '''')   ' AS TableName,
  t.ItemNumber
FROM @items i
WHERE i.Id IN (
    SELECT t.ItemNumber
    FROM '   QUOTENAME(t.Name)   ' t
)
'   ),
    N'
UNION ALL
'   )

....