Finding which database who has a specifik string in one field

• Edvard Korsbæk

  SSCertifiable

  Points: 5994

  More actions

  February 12, 2019 at 3:48 am

  #376955

  Hi all!
  I have +200 DB's on my server.
  Most (but not all!) of them has this  table:

  CREATE TABLE [dbo].[institution](
      [INST_INST_ID] [INT] NOT NULL,
      [INST_SERVERNAVN] [CHAR](100) NULL,
      [INST_BRUGERNAVN] [CHAR](20) NULL,
      [INST_DATABASENAVN] [CHAR](100) NULL,
      [INST_PASSWORD] [CHAR](30) NULL,
      [INST_INST_NAVN] [CHAR](100) NULL,
      [INST_ADRESSE] [CHAR](100) NULL,
      [INST_POSTNR] [INT] NULL,
      [INST_TELEFON] [CHAR](20) NULL,
      [INST_FAX] [CHAR](20) NULL,
      [INST_EMAIL] [CHAR](200) NULL,
      [INST_LEDER] [CHAR](100) NULL,
      [INST_EAN] [CHAR](30) NULL,
      [INST_CVR] [CHAR](20) NULL,
      [INST_LOVOMRAADE] [INT] NULL,
      [INST_NORMERING] [INT] NULL,
      [INST_DRIFTPARAGRAF] [CHAR](20) NULL,
      [INST_TYPE] [INT] NULL,
      [DEPARMENTCODE] [CHAR](6) NULL,
  CONSTRAINT [INST_BY_INST_ID] PRIMARY KEY CLUSTERED
  (
      [INST_INST_ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

  Where on of them has the value 'Fynbohus' in  [INST_INST_NAVN] [CHAR](100) NULL,
  i.e.  select Count(INST_INST_NAVN) from dbo.institution where INST_INST_NAVN = 'Fynbohus'
  Would return 1 from the correct db

  I want to find that database

  How to?

  Best regards

  Edvard Korsbæk

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 12, 2019 at 8:38 am

  #2021785

  something like this should work:
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

  IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL
DROP TABLE [dbo].[#Results]
GO
CREATE TABLE [dbo].[#Results] (
[ResultsID]  INT     IDENTITY(1,1)    NOT NULL,
[DatabaseName] VARCHAR(128)           NULL,
[RecordCount] INT              NULL)

  EXECUTE sp_msForEachDB
'
USE [?]
IF EXISTS(SELECT *
    FROM [?].sys.tables
    WHERE name = ''institution'' --targettable
    AND SCHEMA_ID = 1 --dbo
    )
    EXECUTE(''INSERT INTO #Results(DatabaseName,RecordCount)
    SELECT
     DB_NAME(),
     COUNT(*)
    FROM [?].dbo.institution MyAlias
    where MyAlias.INST_INST_NAVN = ''''Fynbohus'''' ;'')
'
SELECT * FROM #Results WHERE RecordCount > 0

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ScottPletcher

  SSC Guru

  Points: 101105

  More actions

  February 12, 2019 at 9:33 am

  #2021803

  Sounds like you are looking only for a single match.  If so,you should skip the dbs after a match if found:

  
IF OBJECT_ID('tempdb.dbo.#stop_flag') IS NOT NULL
    DROP TABLE #stop_flag;
CREATE TABLE #stop_flag ( stop_flag bit NOT NULL DEFAULT 0 );
INSERT INTO #stop_flag DEFAULT VALUES;

  EXEC sp_MSforeachdb '
IF EXISTS(SELECT 1 FROM #stop_flag WHERE stop_flag = 0)
AND EXISTS(SELECT 1 FROM [?].sys.tables WHERE name = ''institution'')
BEGIN
    DECLARE @sql varchar(8000)
    SET @sql = ''IF EXISTS(SELECT 1 FROM [?].dbo.mul
        WHERE INST_INST_NAVN = ''''Fynbohus'''')
        BEGIN PRINT ''''?''''; UPDATE #stop_flag SET stop_flag = 1 END /*IF*/''
    --PRINT @sql
    EXEC(@sql)
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".

• Edvard Korsbæk

  SSCertifiable

  Points: 5994

  More actions

  February 12, 2019 at 10:05 am

  #2021810

  Thanks - I have found the DB, Which was bbj_fynbohus.

  As I see it, the trick is sp_MSforeachdb, which I will lookup, so i do not need too ask next time.

  Thanks for the help!

  Best regards

  Edvard