Discovering all workflows in shareoint farm.

I got a question from user to identify all workflow in my Sharepoint environment. I tried to identify some out of box option but could not find one. After that I have to put my DB cap on and write query on backend content database of MOSS farm to get list all sites and sub containers which are having active workflow running in them. Here is the query :

select distinct(w.fullurl) as SiteName, L.tp_Title as ListName from webs w,workflow wf , Alllists L where w.id=wf.webid and w.siteId=wf.siteId and wf.listId=L.tp_Id

Another query to get list of all sites and subsites and there approximate data size in sharepoint farm through content database is.

select w.fullurl as URL,d.webId as WebID,sum(d.size/1024) as Size
from docs as d ,webs as w
where d.webId=w.Id
group by d.webId ,w.fullurl
order by w.fullurl

Publishing Site Template in MOSS 2007

Last week I had a hard time tracking an issue which at last found out to be database goof up. We had MOSS 2007 hosted on VM environment. 'Icing on Cake' is that even Database server is on VM with 300 Gigs of Single Content DB. We were installing infrastructure update in this farm when panick struck. MOSS site do not come up at all. At DB side, DBA informed that there were excessive locking going in the background and if they kill that Job locking stops and site come up almost immediately. On detailed investigation it was found that sub procedure proc_DeleteUrl() was locking the db. On further testing it was revealed that Creating Publishing site trigger this action. After working with Microsoft Support it was identified that DB's were not performing well and maintenance of DB's were creating issues. We ran update statistics command on tables to correct this issue. Details of commands are as follows.

UPDATE STATISTICS ALLDocs WITH FULLSCAN
UPDATE STATISTICS AllUserData WITH FULLSCAN
UPDATE STATISTICS WebPartLists WITH FULLSCAN
UPDATE STATISTICS WebParts WITH FULLSCAN
UPDATE STATISTICS AllDocVersions WITH FULLSCAN
UPDATE STATISTICS AllUserDataJunctions WITH FULLSCAN
UPDATE STATISTICS AllDocStreams WITH FULLSCAN
UPDATE STATISTICS BuildDependencies WITH FULLSCAN
UPDATE STATISTICS AllLinks WITH FULLSCAN
UPDATE STATISTICS RoleAssignment WITH FULLSCAN

MOSS 2007 Table Structure

I always thought of to see table structure of MOSS Databases so as to run some basic queries. Although I know very well that touching DB is not safe enough as it make Microsoft Support Untouchable but still sometime you need to take those chances. I have compiled list of table and tablestructure for all important databases(ConfigDB,ContentDB,SharedServicesDB,SharedSerivicesSearchDB) in MOSS 2007.http://sites.google.com/site/mossforsurecontent/content-for-moss-for-sure/moss-for-sure-content/MOSSDatabaseTableStructure.xls?attredirects=0. More details of Configuration and Content Databases is provided at http://msdn.microsoft.com/en-us/library/ms998452.aspx