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

we are looking for a 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
rastafarayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
'

Open in new window

0
 
rastafarayAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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'') and exists( select null from sys.objects where id = object_id("table_name") and schema_id = user_id("schema_name"))  )
    begin
      select ''not found in ?''
    end
    else
    begin
      select ''found in ?''
    end
  end
'
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
rastafarayAuthor Commented:
is this also acceptable?

if not exists(select * from syscolumns where name = ''sOrderCancelFilter'' and id = object_id(DB_NAME()+''.SR_UserOptions'') )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no. object_id only uses the object name, which does not include the schema name
0
 
rastafarayAuthor Commented:
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'') )
0
 
rastafarayAuthor Commented:
k will try this next....

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


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
syscolumns  does not have the schema_id column (or user_id cvolumn)
0
 
rastafarayAuthor Commented:
any other workaround in being able to pass the dbname as schema name to the 'select * from syscolumns' query?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 ...
0
 
rastafarayAuthor Commented:
got it.

for the following:
user_id("schema_name"))

can i use:

user_id(''?''))

so that the dbname is used?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would only work if the db name is the same as the schema name.
if that is the case, sure
0
 
rastafarayAuthor Commented:
cool - that happens to be the case :)


TY for your help

0
 
rastafarayAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.