Suche von Text in Metadaten von Datenbankobjekten / search text in metadata of database objects

Das nachfolgende T-SQL-Script erlaubt das Suchen eines Texts in den Metadaten von Datenbankobjekten.

The following T-SQL script allows finding a text in the metadata of database objects.

DECLARE @sSEARCHSTRING VARCHAR(255)
 ,@sNOTCONTAIN VARCHAR(255)

SELECT @sSEARCHSTRING = 'KHKArtikel'
 ,@sNOTCONTAIN = 'KHKArtikelVarianten'

SELECT DISTINCT sysobjects.NAME AS [Object Name]
 ,CASE 
  WHEN sysobjects.xtype = 'P'
   THEN 'Stored Proc'
  WHEN sysobjects.xtype = 'X'
   THEN 'Stored Proc extended'
  WHEN sysobjects.xtype = 'TF'
   THEN 'Function'
  WHEN sysobjects.xtype = 'TR'
   THEN 'Trigger'
  WHEN sysobjects.xtype = 'V'
   THEN 'View'
  WHEN sysobjects.xtype = 'IF'
   THEN 'Function inline table'
  WHEN sysobjects.xtype = 'FN'
   THEN 'Function scalar'
  END AS [Object Type]
 ,syscomments.TEXT
FROM sysobjects
 ,syscomments
WHERE sysobjects.id = syscomments.id
 AND sysobjects.type IN (
  'P'
  ,'X'
  ,'TF'
  ,'TR'
  ,'V'
  ,'IF'
  ,'FN'
  )
 AND sysobjects.category = 0
 AND CHARINDEX(@sSEARCHSTRING, syscomments.TEXT) > 0
 AND CHARINDEX(@sNOTCONTAIN, syscomments.TEXT) = 0
ORDER BY sysobjects.NAME