Getting Sitecore out of SQL

In a recent project I needed to call Sitecore via SQL. Here are a few snippets that will get most people started.

It should be noted that getting Sitecore data via SQL is not always faster than using the API. And when you use SQL you lose all of the caching that is built into Sitecore. I would only use SQL for integration.

http://adeneys.wordpress.com/2010/09/17/sitecore-api-vs-sql/

getting an item by ID

SELECT TOP 1000 [ID]
      ,[Name]
      ,[TemplateID]
      ,[MasterID]
      ,[ParentID]
      ,[Created]
      ,[Updated]
  FROM [MyWebSite].[dbo].[Items]
  WHERE ParentID = '{04260FEB-F478-457A-9457-305D83B71018}'

Get the children of an Item by parent ID

SELECT TOP 1000 [ID]
      ,[Name]
      ,[TemplateID]
      ,[MasterID]
      ,[ParentID]
      ,[Created]
      ,[Updated]
  FROM [MyWebSite].[dbo].[Items]
  WHERE ParentID = '{04260FEB-F478-457A-9457-305D83B71018}'

Get all the versioned fields for an item

SELECT TOP 1000 
	  i.Name
	  , v.[Id]
      ,[ItemId]
      ,[LANGUAGE]
      ,[FieldId]
      ,[Value]
      ,v.[Created]
      ,v.[Updated]      
  FROM [MyWebSite].[dbo].[versionedFields] v
  JOIN [MyWebSite].[dbo].Items i 
	ON v.FieldId = i.ID
  WHERE ItemId = '04260FEB-F478-457A-9457-305D83B71018'