0

I have more than 100 databases (all have the same schema [dbo.]) on the same SQL Server. After upgrading program which is using these databases, we discovered few databases are not "upgraded" with our script. Now I have to find all not upgraded databases and I will have to use special migration script on every not upgraded database.

Information about "upgrade" is stored in every database, in table Product_Version in column Migrations_History - if database is upgraded, we have a new result in this column for example:

"20230724_v5.0.0"

Can you please help me to write SQL query which will return name of every database on SQL Server without result "20230724_v5.0.0" in table Product_Version in column Migrations_History?

Thank you very much

I was trying to change this query, but I think it is wrong way. I am quite new in whole this stuff.

SELECT      
    '[' + SCHEMA_NAME(schema_id) + ']' + '.' +  '[' + db_name() + ']' as "Database_Schema_Name",
    t.name AS 'Table_Name',
    c.name  AS 'Column_Name'
FROM        
    sys.columns c
JOIN
    sys.tables t ON c.object_id = t.object_id
WHERE       
    t.name LIKE '%Migrations_History%'
    AND c.name LIKE '%Product_Version%'

I was also trying to mix it with:

SELECT name FROM sys.databases

but I couldn't make it work.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NewOne
  • 1
  • 1
  • You will need to dynamically build your query from `sys.databases` to query `sys.columns` and `sys.tables` to build a suitable `exists` query for each. Probably better though to roll back your migration script and debug the issue? – Stu Jul 24 '23 at 15:19

1 Answers1

0

You could use this standard template:

declare @name nvarchar(776)
declare cr_x cursor read_only forward_only local static for
    select QUOTENAME(name)
    --select *
    from sys.databases
    where case when state_desc = 'ONLINE' then object_id(quotename(name) + '.dbo.Migrations_History') end is not null --assumes dbo as schema

open cr_x
while 1 = 1
begin
    fetch next from cr_x into @name
    if @@fetch_status <> 0
        break
        
    declare @sql nvarchar(max) = 'if not exists(select 1 from ' + @name + '.dbo.Migrations_History where product_version = ''20230724_v5.0.0'') 
        select @name as EVERYTHING_BUT_THE_MISSING'
    --print @sql
    exec sys.sp_executesql @sql
    ,   N'@name nvarchar(776)'
    ,   @name = @name
end

close cr_x
deallocate cr_x

it's a cursor that loops every online database that contains your table and checks if some value exists. In that case, it "returns" name of the database

siggemannen
  • 3,884
  • 2
  • 6
  • 24
  • Thank You very much for so fast response! Unfortunately, this query lists me all my databases from server so it looks like this part: where product_version = ''20230724_v5.0.0'' doesn't work. – NewOne Jul 24 '23 at 16:37
  • You can print the query it generates by removing the comment and probably it's easy to figue out what the issue is? – siggemannen Jul 24 '23 at 16:38
  • i don't know exactly how the data looks in those tables so it's hard to debug if the query itself looks alright – siggemannen Jul 24 '23 at 16:39