Scripts

Find And Extract Sharepoint Files With TSQL

Here is some quick SQL logic to help you find extract, export, or otherwise download Sharepoint documents with SQL. The sp_oamethod has been around for quite some time, and delighted it’s still working. You never know when MS will pull these procedures.

use [WSS_Content];
set nocount on
go
-- look for specific document files in sharepoint
select 
	'database'	= db_name()
,	'time_created'	= left(alldocs.timecreated, 19)
,	'kb'		= (convert(bigint,alldocstreams.size))/1024
,	'mb'		= (convert(bigint,alldocstreams.size))/1024/1024
,	'list_name'	= alllists.tp_title
,	'file_name'	= alldocs.leafname
,	'url'		= alldocs.dirname

from 
	alldocs join alldocstreams	on alldocs.id=alldocstreams.id 
	join alllists			on alllists.tp_id = alldocs.listid
where
	alldocstreams.[size] > 2048 -- looking for files under 2 mb
	and [alllists].[tp_title]	like '%training%'
	and [alldocs].[leafname]	like '%tutorial%'
	and right([alldocs].[leafname], 2) in ('oc', 'cx', 'df', 'sg', 'xt')
order by
	alldocs.timecreated desc


-- extract files using the leafname and dirname as identifiers
declare	@object_token		int
declare	@destination_path	varchar(255)
declare @content_binary		varbinary(max)
set	@destination_path	= '\\MyServerName\W$\Sharepoint_Extraction\my_tutorial.pdf'

select	@content_binary		= alldocstreams.content from alldocs join alldocstreams	on alldocs.id = alldocstreams.id join alllists on alllists.tp_id = alldocs.listid
where	
	alldocs.leafname    = 'my_tutorial.pdf'
	and alldocs.dirname = 'Site/Process/Tutorials/TechnicalDocuments/How-To/Guides'

exec sp_oacreate 'adodb.stream', @object_token output
exec sp_oasetproperty	@object_token, 'type', 1
exec sp_oamethod	@object_token, 'open'
exec sp_oamethod	@object_token, 'write',		null, @content_binary
exec sp_oamethod	@object_token, 'savetofile',	null, @destination_path, 2
exec sp_oamethod	@object_token, 'close'
exec sp_oadestroy	@object_token

-- see files at the destination
declare @check_files	varchar(255)
set		@check_files	= '\\MyServerName\W$\Sharepoint_Extraction\'
exec	master..xp_dirtree @check_files, 1,1
go
Advertisements

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 )

w

Connecting to %s