Select Feature Class Metadata from ArcSDE

ListFeatureClasses.sql

-- Selects the feature classes in a database. Returns names, geometry types, and title.
SELECT 
	i.[Name], 
	i.[Definition].value(N'(/DEFeatureClassInfo/ShapeType)[1]', N'varchar(30)') as GeometryType,
	i.[Documentation].value(N'(/metadata/idinfo/citation/citeinfo/title)[1]', N'varchar(255)') as Title
FROM dbo.GDB_ITEMS AS i INNER JOIN 
	dbo.GDB_ITEMTYPES AS t ON i.Type = t.UUID
WHERE t.Name = 'Feature Class'
ORDER BY i.Name

Select dates from metadata.sql

-- Selects dates extracted from the metadata.
SELECT 
	i.[Name]
	,[PhysicalName]
	,t.[Name] as [Type]
	,[Path]
	-- Note that if the date fields are present but have an unexpected value the query will fail.  
	-- You can use 'nvarchar(max)' instead of 'date' if you encounter this failure in order to make
	-- the query work. (Or if SQL has some sort of equivalent to Date.TryParse(), that could be used here.)
	,[Documentation].value(N'(/metadata/idinfo/timeperd/timeinfo/sngdate/caldate)[1]', N'date') [SingleDate]
	,[Documentation].value(N'(/metadata/idinfo/timeperd/timeinfo/rngdates/begdate)[1]', N'date') [BeginDate]
	,[Documentation].value(N'(/metadata/idinfo/timeperd/timeinfo/rngdates/enddate)[1]', N'date') [EndDate]
FROM dbo.GDB_ITEMS AS i INNER JOIN
	dbo.GDB_ITEMTYPES AS t ON i.Type = t.UUID

Select Items with no Metadata.sql

-- Selects items in a geodatabase with either NULL metadata or an empty metadata tag.
SELECT 
      i.[Name]
      ,[PhysicalName]
	  ,t.[Name] as [Type]
      ,[Path]
      ,[Documentation]
  FROM dbo.GDB_ITEMS AS i INNER JOIN
        dbo.GDB_ITEMTYPES AS t ON i.Type = t.UUID
  WHERE Documentation IS NULL OR DATALENGTH(Documentation) <= 135 -- '<metadata xml:lang="en" />'

Select metadata title.sql

SELECT 
      i.[Name]
      ,[PhysicalName]
	  ,t.[Name] as [Type]
      ,[Path]
      ,[Documentation].value(N'(/metadata/idinfo/citation/citeinfo/title)[1]', N'varchar(255)') [Metadata title]
  FROM dbo.GDB_ITEMS AS i INNER JOIN
        dbo.GDB_ITEMTYPES AS t ON i.Type = t.UUID
  WHERE t.Name = 'Feature Class' 
  AND 
  Documentation IS NOT NULL 
  AND 
  DATALENGTH(Documentation) > 135 -- '<metadata xml:lang="en" />'

Select only items with metadata.sql

-- Selects items in a geodatabase with either NULL metadata or an empty metadata tag.
SELECT 
      i.[Name]
      ,[PhysicalName]
	  ,t.[Name] as [Type]
      ,[Path]
      ,[Documentation]
  FROM dbo.GDB_ITEMS AS i INNER JOIN
        dbo.GDB_ITEMTYPES AS t ON i.Type = t.UUID
  WHERE Documentation IS NOT NULL AND DATALENGTH(Documentation) >= 135 -- '<metadata xml:lang="en" />'

SelectMetadata10.sql

SELECT     
	Items.ObjectID
	,Items.Name
	,Items.Documentation -- This field will contain the XML metadata.
	-- , Items.Shape 
	,ItemTypes.Name ItemType
FROM
         dbo.GDB_ITEMS AS Items INNER JOIN
        dbo.GDB_ITEMTYPES AS ItemTypes ON Items.Type = ItemTypes.UUID 
WHERE   (ItemTypes.Name = 'Feature Class')  --(Items.Type = '70737809-852C-4A03-9E22-2CECEA5B9BFA')