SQL Stuff

Get Specific File Name From Sharepoint Database

sharepoint_document_extraction

Here’s a quick query to get all documents across Sharepoint content databases based on a wildcard name you specify. Just remember to change the @wild_card variable to the document(s) you’re looking for.
The data is pulled into a temp table called #collection

Note: If you have a wide range of Sharepoint content databases on a single server; this will take some time to run but the results are worth it as you can run additional queries on the temp table, or port it into another database if needed. Excellent way to manage and administer document content especially considering that image files are many times larger than text or basic document files. In this case; you can identify what possible objects may be ‘compressed’, archived or removed in order to aggressively manage capacity.

use master;
set nocount on

declare	@wild_card	varchar(255)
declare @get_doc	varchar(max)
set	@wild_card	= 'MyFileName'
set	@get_doc	= ''
select	@get_doc	= @get_doc +
'
use [' + [name] + '];' + char(10) + 
'
 select 
	db_name()
,	''list_name''	= alllists.tp_title
,	''file_name''	= alldocs.leafname
,	''url''		= alldocs.dirname
,	''document content (binary)''	= alldocstreams.content
from 
	alldocs join alldocstreams	on alldocs.id=alldocstreams.id 
	join alllists			on alllists.tp_id = alldocs.listid
where
	alldocs.leafname like ''%' + @wild_card + '%''
	or
	alldocs.dirname like ''%' + @wild_card + '%''
'
from
	sys.databases
where
	[name] like '%content%'
order by
	[name] asc

if object_id('tempdb..#collection') is not null
drop table	#collection
create table	#collection
(
	@wild_card[database]	varchar(255)
,	[list_name]		varchar(255)
,	[file_name]		varchar(510)
,	[url]			varchar(510)
,	[document_content]	varbinary(max)
)
insert into	#collection
exec		(@get_doc)

select * from #collection order by [list_name], [file_name] asc

Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s