SQL Update Join Multiple Tables (if field exists)

Question

This is my first post to StackOverflow. I've been using this amazing resource for a number of years to answer hundreds of SQL and PowerShell questions, however this one has me stumped for a number of days.

I am using SQL Server 2014 SP2 and I am trying to do an update to DATABASE1, FIELD1, then FIELD2 then FIELD3 from multiple other database.

FIELD1 may exist in one of multiple other databases. FIELD1 may not exist in ALL databases - which is where I have the problem.

Database Design Link

I have the following (anonymised) query and it appears to be working:

EXEC sp_MSforeachdb 'IF ''?''  IN (''DATABASE2'',''DATABASE3'',''DATABASE4'')  
BEGIN  
  UPDATE DATABASE1.PARAMETER 
  SET B.[VALUE] = A.[FIELD1] 
  FROM DATABASE1.TABLE1 B 
  INNER JOIN ?.dbo.[TABLE2] A 
  ON A.JOINVALUE = B.JOINVALUE
  WHERE B.COLUMN2 = ''SOMETHING'' 
  AND COLUMN3= ''PF.T.FIELD1'' 
END ;'

Until I get to say FIELD8, as it exists in DATABASE1 but not in DATABASE2, DATABASE3 or DATABASE4. I then get the following error:

Msg 207, Level 16, State 1, Line 30     
Invalid column name 'FIELD8'.

From my Google and StackOverflow searches, I've tried to use (for the first time) a:

IF EXISTS (SELECT COLUMN1 FROM Database2.Table2 WHERE Column1='Field8')
  EXEC .......

But that's where I started to really struggle.

Hope the above makes sense. Any tips or assistance would be greatly appreciated.

N.B. I have about 3,000 fields in Database1 which require updating. I've so-far built all my UPDATE statements dynamically.

Answer 1

You can create stored proc, that will search for columns and tables in system tables:

ALTER PROCEDURE dbo.check_table_exists
    -- Add the parameters for the stored procedure here
        @table_name nvarchar(255),
        @column_name nvarchar(255)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @SQLString nvarchar(max),
            @ParmDefinition nvarchar(500) = N'@table_name nvarchar(255), @column_name nvarchar(255)';  

    IF OBJECT_ID(N'tempdb..#check_column_exists') is not null DROP TABLE #check_column_exists
    CREATE TABLE #check_column_exists (
        db nvarchar(500) NULL,
        column_exists bit NULL
    )

    SELECT @SQLString = 
    (
        SELECT   N'USE '+QUOTENAME([name]) +'; '+
        'INSERT INTO #check_column_exists '+
        'SELECT '''+[name]+''' as [db], '+
        '       COUNT(*) as column_exists ' +
        'FROM sys.tables t ' +
        'INNER JOIN sys.columns c ' +
        '   ON t.[object_id] = c.[object_id] ' +
        'WHERE t.[name] = @table_name and c.[name] = @column_name; '
        FROM sys.databases
        WHERE [name] NOT IN (
            'msdb',
            'model',
            'tempdb',
            'master'
        )
        FOR XML PATH('')
    ) + 'SELECT [db] FROM #check_column_exists WHERE column_exists = 1; DROP TABLE #check_column_exists;'

    EXEC sp_executesql @SQLString, @ParmDefinition, @table_name = @table_name, @column_name = @column_name
END
GO

You can change it to search only for columns and output the database and table name or whatever.

The output is:

db
-----------
DATABASE1
DATABASE4
...
etc

After that you can write this to table and use for dynamic SQL update query:

DECLARE @table_name nvarchar(255) = 'SomeTable',
        @column_name nvarchar(255) = 'SomeField'

DECLARE @results TABLE (
    db nvarchar(500)
)

INSERT INTO @results
EXEC dbo.check_table_exists @table_name, @column_name

--...Here goes building of dynamic SQL query to update data

Answer 2

First, sp_MSforeachdb is not reliable. For a working alternative, check here: Making a more reliable and flexible sp_MSforeachdb - Aaron Bertrand

Second, you can use system views to check if a column exists in a given table using sys.columns like so:

if exists (
  select 1 
  from sys.columns c 
  where c.name = 'pilots_id'  /* column name */
    and c.object_id = object_id(N'pilots') /* table name */
    )
    begin
    select 'Pilots_Id exists' /* do stuff */
    end

rextester demo: http://rextester.com/UUXCB18567