How to get sharepoint list data on sql by calling content db

Create a view for accessing list items in db



/*lsitid is GUID of list name */

ALTER  View [dbo].[ViewName] AS   /*Say Vehicle */
 SELECT 
  nvarchar1 as VehicleName,
  CAST(tp_ID AS INT) as ID,
  int2 as VehicleCategory,
  float1 as SortOrder
  
FROM

 [WSS_Content_siteName].[dbo].[AllUserData] AS list

WHERE

 list.tp_iscurrentversion=1 AND list.tp_RowOrdinal=0

AND list.tp_CalculatedVersion=0 AND list.tp_DeleteTransactionId=0x AND

    list.tp_listid='88443F36-7C2F-4779-958F-56C38B9923423'

If you did any changes on list item and you need to select only the approved changes then you must add another filter condition and tp_ModerationStatus=0

Thus


/*lsitid is GUID of list name */
ALTER  View [dbo].[ViewName] AS   /*Say Vehicle */
 SELECT 
  nvarchar1 as VehicleName,
  CAST(tp_ID AS INT) as ID,
  int2 as VehicleCategory,
  float1 as SortOrder
  
FROM
 [WSS_Content_siteName].[dbo].[AllUserData] AS list
WHERE
 list.tp_iscurrentversion=1 AND list.tp_RowOrdinal=0
AND list.tp_CalculatedVersion=0 AND list.tp_DeleteTransactionId=0x AND
    list.tp_listid='88443F36-7C2F-4779-958F-56C38B9923423'
and tp_ModerationStatus=0



Suppost the VehicleACtegory is a look up field with multiple selection then you will not get that value on the above query. In order to select the lookupdata
  you need the below query


/* Here AUDJ.tp_FieldId is the GUID of VehicleCategory field in Vehicle List and AUD.tp_listid is GUID of vehicle list */
CREATE  View [dbo].[VehicleCategory] AS
 SELECT 
AUD.tp_ID as ID,
  nvarchar1 as Title,
  CAST(AUDJ.tp_ID AS INT) as [VehicleCategory],
  float1 as SortOrder
   FROM
[WSS_Content_siteName].[dbo].[AllUserDataJunctions] AUDJ, [WSS_Content_siteName].[dbo].[AllUserData] AUD
  WHERE
 AUDJ.tp_iscurrentversion=1 AND AUDJ.tp_CalculatedVersion=0 AND AUDJ.tp_DeleteTransactionId=0x
 and AUDJ.tp_FieldId like '6983dc8a-7e23-41d8-a8ba-ee53339c2bb7'
 AND AUD.tp_listid='88443F36-7C2F-4779-958F-56C38B994B78'
 AND AUD.tp_iscurrentversion=1 AND AUD.tp_rowOrdinal=0 AND AUD.tp_CalculatedVersion=0 AND AUD.tp_DeleteTransactionId=0x
 AND AUDJ.tp_DocId = AUD.tp_DocId