IF field exist in multiple dbs on server

• sebekkg

  SSC Enthusiast

  Points: 127

  More actions

  January 23, 2019 at 12:56 am

  #379114

  Hello everyone i'm new on forum and i really need your help. 

  I want to see if field exist in certain table but across mutliple dbs. I have same tables in every db but to see in which db and table does that particular field exist. also for db name should be something like len(db_name)=9 and right(db_name,5)='_prod' .

  Thanks for any help.

• ScottPletcher

  SSC Guru

  Points: 101130

  More actions

  January 23, 2019 at 6:59 am

  #2019800

  
EXEC sp_MSforeachdb '
IF LEN(''?'') = 9 AND RIGHT(''?'', 5) = ''_prod''
BEGIN
    USE [?];
    IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(''certain_table'')
    AND name = ''certain_column'')
        PRINT ''Database ? has table certain_table with column certain_column.''
END /*IF*/

  '

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• sebekkg

  SSC Enthusiast

  Points: 127

  More actions

  January 23, 2019 at 8:14 am

  #2019819

  ScottPletcher - Wednesday, January 23, 2019 6:59 AM

  
  EXEC sp_MSforeachdb '
  IF LEN(''?'') = 9 AND RIGHT(''?'', 5) = ''_prod''
  BEGIN
      USE [?];
      IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(''certain_table'')
      AND name = ''certain_column'')
          PRINT ''Database ? has table certain_table with column certain_column.''
  END /*IF*/

  '

  Thanks for the help however in sql management studio I receive message "commands completed successfully.".

• ScottPletcher

  SSC Guru

  Points: 101130

  More actions

  January 23, 2019 at 8:18 am

  #2019820

  sebekkg - Wednesday, January 23, 2019 8:14 AM

  ScottPletcher - Wednesday, January 23, 2019 6:59 AM

  
  EXEC sp_MSforeachdb '
  IF LEN(''?'') = 9 AND RIGHT(''?'', 5) = ''_prod''
  BEGIN
      USE [?];
      IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(''certain_table'')
      AND name = ''certain_column'')
          PRINT ''Database ? has table certain_table with column certain_column.''
  END /*IF*/

  '

  Thanks for the help however in sql management studio I receive message "commands completed successfully.".

  Then it didn't find a match.

  Did you change  certain_table  to the actual table name you are looking for?  And the  certain_column  name also, to the column name you want.  You didn't specify the actual names, so I had to use sample names instead.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 23, 2019 at 8:20 am

  #2019821

  sebekkg - Wednesday, January 23, 2019 8:14 AM

  ScottPletcher - Wednesday, January 23, 2019 6:59 AM

  
  EXEC sp_MSforeachdb '
  IF LEN(''?'') = 9 AND RIGHT(''?'', 5) = ''_prod''
  BEGIN
      USE [?];
      IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(''certain_table'')
      AND name = ''certain_column'')
          PRINT ''Database ? has table certain_table with column certain_column.''
  END /*IF*/

  '

  Thanks for the help however in sql management studio I receive message "commands completed successfully.".

  You probably don't have a table called certain_table. Use the correct name for your table. You can also use an ELSE to show a different message if the column is not found.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sebekkg

  SSC Enthusiast

  Points: 127

  More actions

  January 23, 2019 at 8:45 am

  #2019826

  I understood and changed names for table and field. But still with field that is id for that table again I receive same message. because when i run it on one db with information_schema i get results for that ID filed.

• sebekkg

  SSC Enthusiast

  Points: 127

  More actions

  January 24, 2019 at 7:07 am

  #2019948

  Thanks everyone for assistance but i couldn't get results so i used
  select distinct TABLE_CATALOG, case
 when exists (
  SELECT 1
  FROM Sys.columns c
  WHERE c.[object_id] = OBJECT_ID('dbo.wanted_table')
   AND c.name = 'wanted_field'
 )
 then 'yes'
 else 'no'
end as yes_no from INFORMATION_SCHEMA.COLUMNS 
  working trough python.

• below86

  SSChampion

  Points: 11553

  More actions

  January 24, 2019 at 7:26 am

  #2019950

  Search for table name across all databases on serve:
  

  CREATE TABLE#temp_list

  (

  db_names        varchar(500),

  tbl_names        varchar(500)

  )

  ;

  EXEC sp_msforeachdb'INSERT INTO #temp_list SELECT "?" AS db_names, name AS tbl_name FROM[?].sys.tables'

  ;

  --SELECT COUNT(*)

  --FROM #temp_list

  --;

  SELECT *

  FROM #temp_list

  WHERE tbl_names ='DwWkStatMessage'

  -- WHERE tbl_namesLIKE '%LobXFORM%'  /* Use if you don'tknow the exact name  */

  ;

  This will search across all databases for a column name.
  CREATE TABLE #temp_list
(
    db_names    varchar(500),
    tbl_names    varchar(500),
    column_name    varchar(500)
)
;

  EXEC sp_msforeachdb 'INSERT INTO #temp_list SELECT "?" AS db_names, tbl.name AS tbl_name, cls.name as column_name FROM [?].sys.tables tbl INNER JOIN [?].sys.columns cls ON cls.object_id = tbl.object_id '

  SELECT db_names, tbl_names, column_name
FROM #temp_list
WHERE column_name LIKE '%zip%'

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.