Report Documents Present in a MOSS 2007 / WSS 3.0 Site

Select

http://intranet/’ + A.tp_DirName + ‘/’ + A.tp_LeafName As ItemURL,

(DS.Size / 1024) AS FileSize_Kb,

D.TimeCreated,

D.MetaInfoTimeLastModified As LastModified,

D.ExtensionForFile

From

   WSS_Content.dbo.AllLists L With (NoLock) join

   WSS_Content.dbo.AllUserData A With (NoLock)

      On L.tp_ID=tp_ListId join

   WSS_Content.dbo.AllDocs D With (NoLock)

      On A.tp_ListID=D.ListID

      And A.tp_SiteID=D.SiteID

      And A.tp_DirName=D.DirName

      And A.tp_LeafName=D.LeafName join

   WSS_Content.dbo.AllDocStreams DS With (NoLock)

      On DS.SiteID=A.tp_SiteID

      And DS.ParentID=D.ParentID

      And DS.ID=D.ID

Where

   DS.DeleteTransactionID=0x

   And D.DeleteTransactionID=0x

   And D.IsCurrentVersion=1

   And A.tp_DeleteTransactionID=0x

   And A.tp_IsCurrentVersion=1

   And D.HasStream=1

   And L.tp_DeleteTransactionId=0x

   And ExtensionForFile not in (‘webpart’,’dwp’,’aspx’,’xsn’,’master’,’rules’,’xoml’, ‘xml’, ‘stp’)

   And A.tp_DirName Like ‘sites/devops%’

   And A.tp_DirName NOT Like ‘sites/devops/_f%’

Order by

A.tp_DirName asc,

DS.Size desc

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: