• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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
0
rastafaray
Asked:
rastafaray
  • 8
  • 6
1 Solution
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now