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.
I assume you are aware that you are NOT supposed to use the core databases AT ALL?
ReplyDeleteWhile writing from the tables is TOTALLY unsupported, reading is also unsupported as it may generate query loads that impact SharePoint.
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