Finding not migrated databases (can be found in one table) in one SQL Server

kq0g1dla 于 2023-08-02 发布在 SQL Server

关注(0)|答案(1)|浏览(106)

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.

sql-server

来源：https://stackoverflow.com/questions/76755790/finding-not-migrated-databases-can-be-found-in-one-table-in-one-sql-server

1条答案

按热度按时间

unftdfkk1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

Finding not migrated databases (can be found in one table) in one SQL Server

1条答案

相关问题

热门标签

最新问答