Extract MOSS 2007 list info via SQL

I want to share a short script developed to extract information and data regarding MOSS 2007 lists.

List settings are available quering dbo.Lists view:

SELECT tp_ID, tp_Fields FROM dbo.lists WHERE tp_Title = 'My List Name'

tp_ID list unique identifier

tp_Fields XML fragment containing list fields info

tp_Title list name

Using SQL Server xml capabilities, the script can extract fields info, relationships and other things.

You can also view the list content because the script queries dbo.UserData view.

You only have to set two variables.

SET @LIST_NAME = ''

SET @MODE = 'I' --[I]=Info; [C]=Choice; [F]=Fields info; [R]=FieldRef info; [D]=Data

--Ex. all lists: LIST_NAME = '' and MODE = 'I'

--Ex. info about lists where name contains "abc": LIST_NAME = 'abc' and MODE = 'I'

--Ex. "My List" Fields Info + FieldRef + Data: LIST_NAME = 'My List' and MODE = 'FRD'

Of course you could turn the script into a stored procedure.

MossListInfo.zip