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
| ItemNumber | Detail |
|---|---|
| 230 | Car |
| 245 | Plane |
Table A1000
| ItemNumber | ProductDescription |
|---|---|
| 350 | Pie |
| 465 | Cherry |
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
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
' )
....
