J'ai trouvé le Cade de réponse utile dans la formulation de mon propre script pour vérifier les objets dans une base de données, j'ai donc pensé que je devais partager mon script ainsi:
DECLARE@Name nvarchar(1000);DECLARE@Sql nvarchar(1000);DECLARE@Result int;DECLARE ObjectCursor CURSOR FAST_FORWARD FORSELECT QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'+ QUOTENAME(OBJECT_NAME(o.object_id))FROM sys.objects o
WHERE type_desc IN('SQL_STORED_PROCEDURE','SQL_TRIGGER','SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','VIEW')--include the following if you have schema bound objects since they are not supportedAND ISNULL(OBJECTPROPERTY(o.object_id,'IsSchemaBound'),0)=0;OPEN ObjectCursor;FETCH NEXT FROM ObjectCursor INTO@Name;WHILE@@FETCH_STATUS =0BEGINSET@Sql = N'EXEC sp_refreshsqlmodule '''+@Name +'''';--PRINT @Sql;BEGIN TRY
EXEC@Result = sp_executesql @Sql;IF@Result <>0RAISERROR('Failed',16,1);END TRY
BEGIN CATCH
PRINT'The module '''+@Name +''' does not compile.';IF@@TRANCOUNT >0ROLLBACKTRANSACTION;END CATCH
FETCH NEXT FROM ObjectCursor INTO@Name;ENDCLOSE ObjectCursor;DEALLOCATE ObjectCursor;
Cela a très bien fonctionné pour moi. (bien que je n'ai pas tous les déclencheurs)
Post-Nice, fonctionne parfaitement! Une plus de plus, vous devez vérifier si l'objet est liée au schéma que sp_refreshsqlmodule va donner de faux négatifs comme liés à un schéma objets ne sont pas pris en charge. Ajoutez donc à votre requête suivants: et isnull(objectproperty(o.object_id,'IsSchemaBound'),0)=0 ;
Je voudrais aussi changer la ligne 6 à la SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']' dans le cas où vous avez des procédures stockées avec un point dans le nom.
Bonne idée! Vous pouvez également utiliser QUOTENAME de citer les noms d'objet en tant que tel. Je vais mettre à jour la réponse.
J'ai essayé ce script sur une procédure stockée qui fait référence à une table inexistante, et il n'a pas la mettre en surbrillance. Bien sûr, si je viens de lancer sp_refreshsqlmodule 'mysp' il n'a pas de rapport cette procédure stockée soit.
Utile script. Deux autres scénarios où il peut échouer: 1 - avez-vous une procédure/fonction nom qui contient un guillemet simple (injection sql via db noms d'objet - woohoo!), 2 - vous avez un scalaire de l'UDF, qui est utilisé dans une VÉRIFICATION de la contrainte sur la colonne de la table. J' j'espère aucun de ces scénarios sont communs...
Un avertissement sur sp_refreshsqlmodule, si un objet est renommé dans SSMS ou renommés à l'aide sp_rename le nom de l'objet dans sys.sql_modules n'est pas modifié. L'exécution de la sp_refreshview ou refreshsqlmodule va reconstruire l'objet à l'aide de quelque sql_modules a pour ce nom, qui pourrait être une définition différente de ce que l'objet est actuellement!
DANGER: Ce sera ré-activer un déclencheur désactivé. J'ai eu un peu de déclencheurs qui ont été désactivé en raison d'une migration d'un système qui était en cours. Ils ont été ré-activé et qu'il a causé à peu près tout à l'écrasement.
Je sais que c'est un vieux post, mais je pense que très utile, une seule question: pourquoi nous avons besoin de ROLLBACK TRANSACTION dans le bloc catch?
Il ne prends pas tout (SQL dynamique ou latebound objets), mais il peut être utile d'appel sp_refreshsqlmodule sur tous les non-liés au schéma de procédures stockées (vous pouvez l'appeler avant pour vous assurer que les dépendances sont mis à jour et ensuite d'interroger les dépendances, ou de l'appeler par la suite et de voir si quelque chose est cassé):
DECLARE@template AS varchar(max)SET@template ='PRINT ''{OBJECT_NAME}''
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
'DECLARE@sql AS varchar(max)SELECT@sql = ISNULL(@sql,'')+ REPLACE(@template,'{OBJECT_NAME}',
QUOTENAME(ROUTINE_SCHEMA)+'.'+ QUOTENAME(ROUTINE_NAME))FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA)+'.'+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound')ISNULLOR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA)+'.'+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound')=0EXEC(@sql
)
Oui, je l'ai mentionné que. Dans mon cas, parce que c'était un ALTER et il n'y avait pas d'autre procédure en utilisant le même nom, j'ai eu une erreur, il ne fait pas de mal quoi que ce soit.
En gros, j'ai fait la même chose, mais il a écrit à CURSORless qui est super rapide.
DECLARE@Name nvarchar(1000);DECLARE@Sql nvarchar(1000);DECLARE@Result int;DECLARE@Objects TABLE(
Id INT IDENTITY(1,1),
Name nvarchar(1000))INSERTINTO@Objects
SELECT QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'+ QUOTENAME(OBJECT_NAME(o.object_id))FROM sys.objects o
WHERE type_desc IN('SQL_STORED_PROCEDURE','SQL_TRIGGER','SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','VIEW')--include the following if you have schema bound objects since they are not supportedAND ISNULL(OBJECTPROPERTY(o.object_id,'IsSchemaBound'),0)=0DECLARE@x INT
DECLARE@xMax INT
SELECT@xMax = MAX(Id)FROM@Objects
SET@x =1WHILE@x <@xMax
BEGINSELECT@Name = Name FROM@Objects WHERE Id =@x
SET@Sql = N'EXEC sp_refreshsqlmodule '''+@Name +'''';--PRINT @Sql;BEGIN TRY
EXEC@Result = sp_executesql @Sql;IF@Result <>0RAISERROR('Failed',16,1);END TRY
BEGIN CATCH
PRINT'The module '''+@Name +''' does not compile.';IF@@TRANCOUNT >0ROLLBACKTRANSACTION;END CATCH
SET@x =@x +1END
À l'aide d'une instruction while pour parcourir un tableau de données est essentiellement le même que l'aide d'un curseur avec fast_forward. Les deux vont de ligne par angoissante ligne (RBAR). C'est une situation où il est acceptable puisqu'il ne peut être remplacé par un ensemble de base de fonctionnement.
Les curseurs ne sont pas très performants. Ils sont la mémoire de porcs.
Ce n'effectuer un peu mieux qu'à l'aide du curseur, même si elle utilise une boucle while.
En plus le script de Michael Petito, vous pouvez vérifier pour des problèmes avec la fin des objets liés au SPs (reporté à la résolution de nom) comme ceci:
-- Based on comment from http://blogs.msdn.com/b/askjay/archive/2012/07/22/finding-missing-dependencies.aspx-- Check also http://technet.microsoft.com/en-us/library/bb677315(v=sql.110).aspxselect o.type, o.name, ed.referenced_entity_name, ed.is_caller_dependent
from sys.sql_expression_dependencies ed
join sys.objects o on ed.referencing_id = o.object_id
where ed.referenced_id isnull
Le problème est qu'il retournera toujours la CTE, les alias et les tables temporaires comme des références nulles.
Même idée, mais plus universelle - vous de vérifier tous les objets avec des organismes
Et il vous montre d'erreur pendant la compilation. C'est vraiment utile après la modification/suppression d'objets/colonnes etc
Il suffit de lancer après le schéma de base de données mise à jour pour s'assurer que tous les corps des objets encore valide
DECLARE@obj_name AS sysname,@obj_type AS sysname
DECLARE obj_cursor CURSORFORSELECT SCHEMA_NAME(o.schema_id)+'.'+ o.name, o.type_desc
FROM sys.objects o
INNERJOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.is_ms_shipped =0AND m.is_schema_bound =0ORDERBY o.type_desc, SCHEMA_NAME(o.schema_id), o.name
OPEN obj_cursor
FETCH NEXT FROM obj_cursor INTO@obj_name,@obj_type
WHILE(@@FETCH_STATUS <>-1)BEGINBEGIN TRY
EXEC sp_refreshsqlmodule @obj_name
--PRINT 'Refreshing ''' + @obj_name + ''' completed'END TRY
BEGIN CATCH
PRINT'ERROR - '+@obj_type +' '''+@obj_name +''':'+ ERROR_MESSAGE()END CATCH
FETCH NEXT FROM obj_cursor INTO@obj_name,@obj_type
ENDCLOSE obj_cursor
DEALLOCATE obj_cursor
Une fois j'ai fait le changement d'un tableau (colonne de renommer, je dois modifier toutes les procédures stockées, les fonctions et les points de vue qui font référence à la colonne de la table. Évidemment, je dois modifier manuellement un par un. Mais ma base de données qui contient des centaines d'objets comme ceux-ci. Donc je voulais m'assurer que j'ai modifié tous les selon les objets. Une solution est de recompiler tous les objets (via un script). Mais recompilation qui se passe sur chaque objet de la prochaine exécution seulement. Mais ce que je veux, c'est de les valider et obtenir les détails maintenant.
Pour que je puisse les utiliser “sp_refreshsqlmodule” au lieu de “sp_recompile”. Cela mettra à jour chaque objet et renvoie une erreur si ce n'est pas l'analyse correctement.
Voici le script ci-dessous;
-- table variable to store procedure namesDECLARE@tblObjects TABLE(ObjectID INT IDENTITY(1,1), ObjectName
sysname)-- get the list of stored procedures, functions and viewsINSERTINTO@tblObjects(ObjectName)SELECT'['+ sc.[name]+'].['+ obj.name +']'FROM sys.objects obj
INNERJOIN sys.schemas sc ON sc.schema_id = obj.schema_id
WHERE obj.[type]IN('P','FN','V')-- procedures, functions, views-- counter variablesDECLARE@Count INT,@Total INT
SELECT@Count =1SELECT@Total = COUNT(*)FROM@tblObjects
DECLARE@ObjectName sysname
-- start the loopWHILE@Count <=@Total BEGINSELECT@ObjectName = ObjectName
FROM@tblObjects
WHERE ObjectID =@Count
PRINT'Refreshing... '+@ObjectName
BEGIN TRY
-- refresh the stored procedureEXEC sp_refreshsqlmodule @ObjectName
END TRY
BEGIN CATCH
PRINT'Validation failed for : '+@ObjectName +', Error:'+
ERROR_MESSAGE()+ CHAR(13)END CATCH
SET@Count =@Count +1END
Si un objet déclenche une erreur que je peux maintenant assister à elle et réparer manuellement le problème avec elle.
Mon approche est un peu différente. J'ai créé le script alter pour un tas de procs dans SSMS, puis attendit quelques secondes afin de SSMS processus d'eux et j'ai obtenu ce que je voulais:
O puis SSMS droite de la bordure d'un red dot pour n'importe quelle ligne dans l'erreur, que je peux facilement vérifier, à corriger et à exécuter plus tard même script pour mettre à jour avec les valeurs correctes.
J'ai trouvé le Cade de réponse utile dans la formulation de mon propre script pour vérifier les objets dans une base de données, j'ai donc pensé que je devais partager mon script ainsi:
SELECT SCHEMA_NAME(o.schema_id) + '.[' + OBJECT_NAME(o.object_id) + ']'
dans le cas où vous avez des procédures stockées avec un point dans le nom.QUOTENAME
de citer les noms d'objet en tant que tel. Je vais mettre à jour la réponse.Il ne prends pas tout (SQL dynamique ou latebound objets), mais il peut être utile d'appel sp_refreshsqlmodule sur tous les non-liés au schéma de procédures stockées (vous pouvez l'appeler avant pour vous assurer que les dépendances sont mis à jour et ensuite d'interroger les dépendances, ou de l'appeler par la suite et de voir si quelque chose est cassé):
En gros, j'ai fait la même chose, mais il a écrit à CURSORless qui est super rapide.
En plus le script de Michael Petito, vous pouvez vérifier pour des problèmes avec la fin des objets liés au SPs (reporté à la résolution de nom) comme ceci:
Couple de façons qui viennent à l'esprit
J'ai essayé le "Cade Roux" Réponse , ça s'est mal passé et je l'ai fixée comme suit
Aucune des réponses ne peut trouver l'erreur résultant de changement de nom ou de la suppression d'une table
mais être heureux, j'ai une solution:
Même idée, mais plus universelle - vous de vérifier tous les objets avec des organismes
Et il vous montre d'erreur pendant la compilation. C'est vraiment utile après la modification/suppression d'objets/colonnes etc
Il suffit de lancer après le schéma de base de données mise à jour pour s'assurer que tous les corps des objets encore valide
Une fois j'ai fait le changement d'un tableau (colonne de renommer, je dois modifier toutes les procédures stockées, les fonctions et les points de vue qui font référence à la colonne de la table. Évidemment, je dois modifier manuellement un par un. Mais ma base de données qui contient des centaines d'objets comme ceux-ci. Donc je voulais m'assurer que j'ai modifié tous les selon les objets. Une solution est de recompiler tous les objets (via un script). Mais recompilation qui se passe sur chaque objet de la prochaine exécution seulement. Mais ce que je veux, c'est de les valider et obtenir les détails maintenant.
Pour que je puisse les utiliser “sp_refreshsqlmodule” au lieu de “sp_recompile”. Cela mettra à jour chaque objet et renvoie une erreur si ce n'est pas l'analyse correctement.
Voici le script ci-dessous;
Si un objet déclenche une erreur que je peux maintenant assister à elle et réparer manuellement le problème avec elle.
Mon approche est un peu différente. J'ai créé le script alter pour un tas de procs dans SSMS, puis attendit quelques secondes afin de SSMS processus d'eux et j'ai obtenu ce que je voulais:
O puis SSMS droite de la bordure d'un red dot pour n'importe quelle ligne dans l'erreur, que je peux facilement vérifier, à corriger et à exécuter plus tard même script pour mettre à jour avec les valeurs correctes.