par , 12/03/2018 à 10h36 (929 Affichages)
Microsoft SQL Server a l'avantage (en comparaison d'Oracle), d'avoir des bases de données très autonomes au sein d'une instance. On peut donc aisément les déplacer d'une instance à l'autre... car chaque base intègre la majeure partie de son méta-modèle (les tables et vues système).
Il y a un prix à payer:
- lors de déplacement de bases, on peut se retrouver avec des désynchronisations de login/users (voir à ce sujet la procédure stockée sp_change_users_login, mais ceci n'est pas le sujet de ce billet),
- on peut avoir de la peine à retrouver un objet si on ne sais pas dans quelle base il se trouve.
La petite procédure stockée ci-dessous permet de palier à ce second point en parcourant toutes les bases à la recherche d'un undex ou d'un objet. Elle a le mérite de rester simple et aisément modifiable.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| CREATE PROC [dbo].[chercheObjet] (@objet sysname)
as
BEGIN
declare @db sysname
declare @sql varchar(MAX)
-- curseur permettant de traverser toutes les bases
declare cur cursor for
select [name] from master.sys.databases
-- création d'une table temporaire stockant les résultats
CREATE TABLE #result (db sysname, obj char, res int)
OPEN cur
FETCH cur INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='insert into #result select '''+@db+''',''I'', count(*) from ['+@db+'].sys.indexes where name='''+@objet+''''
EXECUTE (@sql)
set @sql='insert into #result select '''+@db+''', [type], count(*) from ['+@db+'].sys.objects where name='''+@objet+''' group by type'
EXECUTE (@sql)
FETCH cur INTO @db
END
-- supprimer les lignes non relevantes
delete #result where res=0
--afficher le résultat
select * from #result
-- nettoyer les ressources
drop table #result
CLOSE cur
DEALLOCATE cur
END |
Il y a bien la procédure stockée sp_MSforeachdb qui permet de boucler sur les bases, mais je préfère personnellement le curseur qui est plus clair qu'une maxi-chaîne SQL à encapsuler dans une procédure stockée...