Friday, September 13, 2013

SharePoint & Project Server - Finding data in database

I have already written a couple of posts on how to find a specified type of data in SharePoint database.

This is the post I wrote on how to find documents stored in Document libraries in SharePoint and this is the post on how to find the items stored in SharePoint lists.

The fact is that, both SharePoint and Project Server databases are very tricky to work with. SharePoint has only one database, and all data is located in that single database and you need some time to figure out its schema and how everything works.


Project Server, on the other hand, has four databases and data is really scattered in these databases.

If you need to make a Report of some sort (for example in Reporting Services), you will need to connect to these databases and fetch data directly from them.

But, sometimes, there is data you see in SharePoint or Project Server that you just can't find in database.

Let's say you have defined new text column on your SharePoint list that contains text "My Custom Text" and you need it for your report and you can't find it in the database.

In that case, the best way is to search the entire database for this specified string. This is the SQL query that searches the entire database for string "My Custom Text". This isn't my query, I found it in on Stackoverflow forum, I am using it here only just as an example:



DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%My Custom Text%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM @Results


This query will return table name and column name in that table where data is stored.

2 comments:

  1. I assume you are aware that you are NOT supposed to use the core databases AT ALL?

    While writing from the tables is TOTALLY unsupported, reading is also unsupported as it may generate query loads that impact SharePoint.

    ReplyDelete
    Replies
    1. I am aware, but I have had many demands about finding a specific data (especially in Project Server DBs) and this is the quickest and easiest way to do it.

      Delete