SharePoint List in SQL

In was recently tasked with Exporting a SharePoint list to Excel for users logging in using Forms Authentication.

I scratched my head for a minute and I came up with creating a Reporting Services report that queries the SP DB directly.

Issues: I wanted the query to be dynamic and use the exact same SP columns. So if a user adds a field or changes a column name I don't need to amend the SSRS report.

Solution: I created a Stored Proc, that takes 1 parameter (ListID). The proc gets the fields for the list then builds a query and executes it. The Stored Proc needs to reside on the same Content DB.

Code:

CREATE procedure [dbo].[usp_PrintList] (@ListId uniqueidentifier)
AS
BEGIN
  DECLARE @XMLFields TABLE (Row INT IDENTITY, Field XML);
  DECLARE @xFields XML; 
  SELECT @xFields = (SELECT cast(
      replace(cast(tp_Fields as varchar(max)),'<FieldRef','<Field') 
      as XML) as Fields
      FROM Lists 
      WHERE (tp_ID = @ListId))
 
  INSERT INTO  @XMLFields
    SELECT Tbl.xFlds.query('.') from @xFields.nodes('/Field') as Tbl(xFlds)
 
  DECLARE @sql VARCHAR(8000), @field XML, 
    @colname VARCHAR(30), @Type VARCHAR(30), @dispname VARCHAR(255);
  
  SET @sql = 'SELECT '
 
  DECLARE tmpCursor CURSOR FOR
    SELECT Field, 't1.'+ Field.value('(/Field/@ColName)[1]', 'varchar(max)') , 
      Field.value('(/Field/@Type)[1]', 'varchar(max)'), 
      ' as ['+ isnull(Field.value('(/Field/@DisplayName)[1]', 'varchar(max)'), 
      Field.value('(/Field/@Name)[1]', 'varchar(max)')) +'], '
    FROM @XMLFields
 
  OPEN tmpCursor
  FETCH NEXT FROM tmpCursor INTO @field, @colname, @Type, @dispname
    WHILE @@FETCH_STATUS = 0
    BEGIN
      IF(@colname IS NOT NULL)
      BEGIN  
 
        SET @sql = (CASE @Type
          WHEN 'Lookup' THEN 
            @sql + '(SELECT nvarchar1 FROM UserData WHERE tp_ListId = '''+ 
            (@field.value('(/Field/@List)[1]', 'varchar(max)')) +
            ''' AND tp_ID = '+ @colname +')' + @dispname    
          
          WHEN 'User' THEN 
            @sql + '(SELECT tp_Title FROM 
            UserInfo WHERE tp_ID = '+ @colname +')' + @dispname            
          
          ELSE @sql + @colname + @dispname
          END)
  
      END
      FETCH NEXT FROM tmpCursor INTO @field, @colname, @Type, @dispname
    END
  CLOSE tmpCursor
  DEALLOCATE tmpCursor  
   
  --strip off last comma
  SET @sql = SUBSTRING( RTRIM(@sql), 1, LEN(@sql) - 1 )
  SET @sql = @sql + ' FROM UserData t1 WHERE t1.tp_ListId = '''+ 
      CAST(@ListId AS VARCHAR(50)) +''' AND t1.tp_RowOrdinal = 0'
 
  PRINT @sql
  EXEC(@sql)
END

Limitations:

1. It doesn't get the Display Name for field references (ie not customized field)
2. It only looks at the Title of the lookup list/user, and then only on custom look up.

Example:

MS SQL Management Studio View

SharePoint View

Keywords: Export to spreadsheet, SQL field view, AllUserData

File link