tsql to loop thru all user ('master', 'model', 'msdb', 'tempdb') dbases except and determine which dbases are missing a specific field for a specific tbl

you could check this:

exec sp_msforeachdb ' if ( not ''?'' in ( ''master'', ''msdb'', ''tempdb'', ''model'') )
  begin  
    if not exists(select * from syscolumns where name = ''column_name'' and id = object_id(''table_name'') )
    begin
      select ''not found in ?''
    end 
    else
    begin
      select ''found in ?''
    end 
  end
'

Select allOpen in new window

in our case we need the schema name.  how can we do this?  here is a sample statement that works:

select * from syscolumns where name = 'sFieldName' and id = object_id('schema_name.tbl_name')

good news is that the schema name is the same as the dbname, so this too will work
select * from syscolumns where name = 'sFieldName' and id = object_id('db_name.tbl_name')

so the following line needs a revision.  not entirely sure about the syntax:

if not exists(select * from syscolumns where name = ''column_name'' and id = object_id(''db_name''+'.'+''table_name'') )


ty angelIII

is this also acceptable?

if not exists(select * from syscolumns where name = ''sOrderCancelFilter'' and id = object_id(DB_NAME()+''.SR_UserOptions'') )

no. object_id only uses the object name, which does not include the schema name

though the fld and tbl names are static, the following may not be usable as the schema name for each dbase is unique and not static.
the schema name is the same as the dbname


if not exists(select * from syscolumns where name = ''column_name'' and id = object_id(''table_name'') and exists( select null from sys.objects where id = object_id("table_name") and schema_id = user_id("schema_name"))  )


or


 if not exists(select * from syscolumns where name = ''field_name'' and  schema_id = DBNAME() and id = object_id(''tbl_name'') )

k will try this next....

if not exists(select * from syscolumns where name = ''field_name'' and  schema_id = ? and id = object_id(''tbl_name'') )

syscolumns  does not have the schema_id column (or user_id cvolumn)

any other workaround in being able to pass the dbname as schema name to the 'select * from syscolumns' query?

as I showed in above example, you have to "join" with sysobjects table to check for the schema_id (from the schema name).
so, if you get the schemaname + tablename in 1 string, you have to first "split" it ...

got it.

for the following:
user_id("schema_name"))

can i use:

user_id(''?''))

so that the dbname is used?

that would only work if the db name is the same as the schema name.
if that is the case, sure

cool - that happens to be the case :)


TY for your help

ty