title: How to join sys.databases, sys.tables and sys.columns
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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 &lt; 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 &lt;&gt; 0
  </code></pre>
  
  <p>EDIT: adding some sql generated from query</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>the table of first query doesn't contain 's_dettagli' column</p>
  
  <p>EDIT2: <strong>SOLUTION</strong></p>
  
  <pre><code>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'''' )'' '
  </code></pre>