How to join sys.databases, sys.tables and sys.columns

Question

I have to check for value existence in a subset of tables in a subset of databases of a sql server instance. Beware I need to do this because I have 30 databases with same schema name and similar structure. Querying all databases separately is a waste of time.

The query generates correctly code for existing tables, but the additional check for column existence in table fails. The column in some tables does not exist so the generated code must not include queries on tables without this column.

To solve this I need to realiably find a way to join sys.databases with sys.tables and then sys.columns. Or an alternative way to query all the required databases in a time saving manner.

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      exist INT
    , DB VARCHAR(50)
    , tbname VARCHAR(500)
)

/*tables common root, 
all tables i need to query start with this prefix and a number between 1 and 50
and some resulting tables do not exist
ex: dbo.Z_WBL_ASCHEDA23 exist in wbcto, while dbo.Z_WBL_ASCHEDA23 does not exist in db wbgtg    
*/

DECLARE @TableName NVARCHAR(200) 
SELECT @TableName = 'dbo.Z_WBL_ASCHEDA'

DECLARE @SQL NVARCHAR(MAX)

;WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < 50
)
SELECT @SQL = STUFF((
    SELECT CHAR(13)+'SELECT COUNT(1), ''' + db.name + ''', '''+
        @TableName+CONVERT(VARCHAR, n.n)+'''  FROM ' +@TableName+CONVERT(VARCHAR, n.n) 
        + ' WHERE COALESCE(s_dettagli,'''') = ''CONTROLLATO'' '
        +CHAR(13)
    FROM sys.databases db
    INNER JOIN n ON 1=1
    INNER JOIN sys.tables t ON OBJECT_ID(db.name + '.' + @TableName+CONVERT(VARCHAR, n.n)) IS NOT NULL
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID and c.name = 's_dettagli' 
    /*join on columns not working, generates sql for tables without 's_dettagli' column and query fails*/
    WHERE db.name like 'wb%' --check only databases starting with 'wb'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

select @SQL

INSERT INTO #temp (exist, DB, tbname)              
EXEC sys.sp_executesql @SQL

SELECT * 
FROM #temp t
where exist <> 0

EDIT: adding some sql generated from query

SELECT COUNT(1), 'wb360', 'dbo.Z_WBL_ASCHEDA23'  FROM wb360.dbo.Z_WBL_ASCHEDA23 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'  
SELECT COUNT(1), 'Wbbim', 'dbo.Z_WBL_ASCHEDA32'  FROM Wbbim.dbo.Z_WBL_ASCHEDA32 WHERE COALESCE(s_dettagli,'') = 'CONTROLLATO'

the table of first query doesn't contain 's_dettagli' column

EDIT2: SOLUTION

EXEC sp_MSforeachdb '
IF ''?'' not like ''wb%'' 
    RETURN
USE [?]
EXEC sp_MSforeachtable 
@replacechar = ''!'', 
@command1 = ''SELECT ''''?'''' AS db_name, ''''!'''' AS table_name, COUNT(*) FROM !  '',
@whereand = '' And Object_id In (
                                Select t.Object_id 
                                From sys.objects t
                                INNER JOIN sys.columns c on c.Object_id = t.Object_id
                                Where t.name like ''''Z_WBL_ASCHEDA%''''
                                AND c.name = ''''s_dettagli'''' )'' '

Answer 1

Sys.columns can be joined to sys.tables using the object_id field (the object_id is the representation of the table itself).

sys.tables is run in the context of the database you are querying, hence you cannot see a table contained in another database. sys.databases can be run on any database on an instance and allow you to view other databases on the same instance. As such you don't need to join the table to the database (also the reason why there is no database_id field within sys.tables).

I hope that helps. Any clarification please let me know.