Rechercher les modules qui référencent un objet et les bases qui contiennent une table
Requêtes T-SQL pour trouver les procédures, vues et fonctions qui citent un objet, et les bases de données qui contiennent une table.
Ces requêtes répondent à trois questions : quels modules font référence à un objet, quelle base contient une table, et quelles bases en ligne contiennent une table donnée.
Trouver les modules qui font référence à un objet
La requête cherche dans le texte de chaque module (procédure stockée, fonction, vue, déclencheur) un nom qualifié par le schéma, sous ses quatre variantes avec crochets.
DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo';
DECLARE @OBJECT_NAME VARCHAR(100) = 'CommandExecute';
SELECT
sys.objects.object_id,
sys.schemas.name AS [Schema],
sys.objects.name AS Object_Name,
sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK)
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
(
'#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
)
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name
Trouver la base qui contient une table
sp_MSforeachdb 'SELECT ''?'' AS DB, * FROM [?].sys.tables WHERE name LIKE ''%tablename%'''
Trouver les bases en ligne qui contiennent une table
SELECT name, state_desc
FROM sys.databases
WHERE CASE
WHEN state_desc = 'ONLINE'
THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TableName]', 'U')
END IS NOT NULL
Remarques
- La première requête compare du texte : les commentaires et le SQL dynamique peuvent donc correspondre. Un module qui construit le nom de l'objet à l'exécution n'est pas trouvé.
- La troisième requête ne vérifie que les bases dont l'état est
ONLINE.OBJECT_IDrenvoie NULL lorsque la table n'existe pas dans une base.