CODEWITHSUNDEEP
sqlsql-serversql-server-2008
alwaysVBNET
alwaysVBNET

Reputation: 3310

Searching multiple tables' fields

We have many temporary tables in this naming convention: temp_table_20153112. From those tables, there's a field called Manager. We want to identify those tables where the Manager IS NULL. Here is what I have at the moment which lists all the tables that start with temp_table_ but I do not know how to requery the result-set. Any ideas?

SELECT t.name AS table_name,
       c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name LIKE '%temp_table_%'
    AND c.name = 'Manager'

Upvotes: 1

Views: 37

Answers (2)

Dzmitry Paliakou
Dzmitry Paliakou

Reputation: 1627

DECLARE
    @table_name SYSNAME,
    @sql VARCHAR(2000)
DECLARE tables CURSOR FOR 
    SELECT DISTINCT t.name AS table_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
    WHERE t.name LIKE '%temp_table_%' 
        AND c.name = 'Manager'
OPEN tables 
FETCH NEXT FROM tables INTO @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [Manager] IS NULL) RAISERROR(''' + @table_name + ''',0,1) WITH NOWAIT'
    EXECUTE(@sql)
    FETCH NEXT FROM tables INTO @table_name
END
CLOSE tables
DEALLOCATE tables

Upvotes: 1

Devart
Devart

Reputation: 121912

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
UNION ALL
SELECT TOP(1) ''' + name + ''' AS tbl FROM ' + name + ' WHERE Manager IS NULL'
    FROM (
        SELECT name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
        FROM sys.objects o
        JOIN sys.columns c ON o.[object_id] = c.[object_id]
        WHERE o.name LIKE 'temp_table_%'
            AND c.name = 'Manager'
            AND c.is_nullable = 1
    ) t
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 13, '')

--PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

SELECT TOP(1) '[dbo].[temp_table_2342]' AS tbl FROM [dbo].[temp_table_2342] WHERE Manager IS NULL
UNION ALL
SELECT TOP(1) '[dbo].[temp_table_234]' AS tbl FROM [dbo].[temp_table_234] WHERE Manager IS NULL
UNION ALL
...

Upvotes: 1

Related Questions