Wednesday, August 11, 2010

SQL Scripts

List of all checked out files for an web applications:

SELECT
    sites.FullUrl AS SiteURL, tp_DirName AS Directory,  tp_LeafName AS Document,  [tp_CheckoutUserId] AS UserID

FROM 
[cms-roche.dev].[dbo].[AllUserData]

INNER JOIN 
[cms-roche.dev].dbo.[Sites] on sites.id=alluserdata.tp_SiteId

WHERE
[tp_CheckoutUserId]<>0

 

List of all activated Features for a web applciation:

SELECT 
sites.FullUrl AS SiteCollection,  Webs.FullUrl AS Site,  Features.FeatureId

FROM 
[cms-roche.dev].[dbo].[Features]

INNER JOIN 
[cms-roche.dev].dbo.Sites ON Features.SiteId=sites.Id

INNER JOIN 
[cms-roche.dev].dbo.Webs ON Features.WebId=Webs.Id

WHERE 
Features.FeatureId='00BFEA71-3A1D-41D3-A0EE-651D11570120'