recherche-table-column-procedure-function
/SQL/recherche-table-column-procedure-function.sql
-------------------------------------------------------------------------------
-- Recherche nom table :
-------------------------------------------------------------------------------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%presta%' AND TABLE_SCHEMA = 'dbo' ORDER BY TABLE_NAME
-------------------------------------------------------------------------------
-- Recherche nom colonne :
-------------------------------------------------------------------------------
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%se_id%' AND TABLE_NAME NOT LIKE 'syncobj%' AND TABLE_SCHEMA = 'dbo' ORDER BY TABLE_NAME
-------------------------------------------------------------------------------
-- Recherche nom routine (procédure, fonction) :
-------------------------------------------------------------------------------
SELECT OBJECT_NAME(OBJECT_ID) AS object_name --, definition
FROM sys.sql_modules
WHERE 1=1
AND OBJECT_NAME(OBJECT_ID) LIKE '%nom%'
ORDER BY 1
-------------------------------------------------------------------------------
-- Recherche DANS LE SCRIPT de procédures, fonctions...
-------------------------------------------------------------------------------
-- TOUT objet fonctions, procédure, vues, (trigger ?) :
SELECT OBJECT_NAME(OBJECT_ID) AS object_name --, definition
FROM sys.sql_modules
WHERE 1=1
AND definition LIKE '%se_nom%'
ORDER BY 1
-- fonctions et procédures :
-- Requête mieux que par ROUTINE_DEFINITION car pb de troncage
SELECT DISTINCT sys.sysobjects.name --, sys.syscomments.text, sys.sysobjects.type
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE 1=1
AND (sys.sysobjects.type = 'P' OR sys.sysobjects.type = 'FN')
AND sys.syscomments.text LIKE '%se_nom%'
ORDER BY 1