Get all Site Colletions with Primary and Secondary Contatct User Info from Sharepoint Content Database

 

--Get all site collections with primary secondary owner user info

Select W.FullURl, W.Title, S.OwnerId,

Max(Case When S.OwnerId = U.tp_Id And S.Id = U.tp_siteId then U.tp_Login End) as PrimaryLogin,

Max(Case When S.OwnerId = U.tp_Id And S.Id = U.tp_siteId then U.tp_Title End) as PrimaryName,

Max(Case When S.OwnerId = U.tp_Id And S.Id = U.tp_siteId then U.tp_Email End) as PrimaryEmailAddress,

S.SecondaryContactID,

Max(Case When S.SecondaryContactID = U.tp_Id And S.Id = U.tp_siteId then U.tp_Login End) as SecondaryLogin,

Max(Case When S.SecondaryContactID = U.tp_Id And S.Id = U.tp_siteId then U.tp_Title End) as SecondaryName,

Max(Case When S.SecondaryContactID = U.tp_Id And S.Id = U.tp_siteId then U.tp_Email End) as SecondaryEmailAddress,

HostHeader, DiskUsed, LastContentChange, W.Id as WebId, W.SiteId, S.RootWebId

From dbo.Sites S Inner Join dbo.Webs W On S.RootWebId = W.Id

Inner join dbo.UserInfo U On S.Id = U.tp_SiteId

Where tp_SiteAdmin =1 and tp_IsActive =1

Group By W.Id, W.SiteId, W.FullURl, W.Title, HostHeader, DiskUsed, LastContentChange, S.OwnerId, S.SecondaryContactID, S.RootWebId, S.RootWebId

-- Get site users with roles & groups
Select W.Id as WebId, W.SiteId, W.FullURl, tp_Id, tp_Login, tp_title, tp_Email, tp_DomainGroup,

R.Title as RoleName,  R.RoleId, G.Title as GroupName, GM.GroupId

From Webs W Inner Join Groups G On W.SiteId = G.SiteId

Inner Join GroupMembership GM on GM.SiteId = G.SiteId and GM.GroupId = G.Id

Inner Join UserInfo U on GM.SiteId = U.tp_SiteId and GM.MemberId = U.tp_Id and U.tp_IsActive = 1 and U.tp_Deleted = 0

Inner Join RoleAssignment RA on GM.SiteId = RA.SiteId and GM.GroupId = RA.PrincipalId and W.ScopeId = RA.ScopeId

Inner Join Roles R on R.SiteId = RA.SiteId and R.RoleId = RA.RoleId 

Group By W.Id, W.SiteId, W.FullURl, W.Title, U.tp_Id, U.tp_Login, U.tp_title, U.tp_Email, tp_DomainGroup

,GM.GroupId, R.RoleId, R.Title, G.Title

-- Get Site users not added under any group

Select W.Id as WebId, W.SiteId, W.FullURl, tp_Id, tp_Login, tp_title, tp_Email, tp_DomainGroup,R.Title as RoleName, R.RoleId

From Webs W Inner Join RoleAssignment RA on W.SiteId = RA.SiteId and W.ScopeId = RA.ScopeId

Inner Join Roles R on RA.SiteId = R.SiteId and RA.RoleId = R.RoleId 

Inner Join UserInfo U on RA.SiteId= U.tp_SiteId and RA.PrincipalId = U.tp_Id and U.tp_IsActive = 1 and U.tp_Deleted = 0

Group By W.Id, W.SiteId, W.FullURl, W.Title, U.tp_Id, U.tp_Login, U.tp_title, U.tp_Email, tp_DomainGroup, R.RoleId, R.Title

-- Find a feature on content db

SELECT DISTINCT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS SiteName, 

dbo.Webs.FullUrl AS SiteUrl, dbo.Features.FeatureId, dbo.Sites.HostHeader 

FROM dbo.Features INNER JOIN 

dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId 

Inner Join Sites ON Webs.SiteId = Sites.Id 

WHERE dbo.Features.FeatureId = 'featureId'

  
-- Find webpart on content db

SELECT DISTINCT D.SiteID, D.WebID, W.Title as SiteName, D.DirName as SiteRelativeUrl,

D.LeafName as PageName, S.HostHeader  

FROM dbo.Docs D WITH (nolock) 

INNER JOIN dbo.Webs W WITH (nolock) ON D.WebID = W.Id 

INNER JOIN dbo.WebParts WP WITH (nolock)  ON D.Id = WP.tp_PageUrlID 

INNER JOIN dbo.Sites S WITH (nolock) ON D.SiteId = S.Id 

WHERE WP.tp_ListId Is Null AND WP.tp_Type Is Null AND WP.tp_Flags Is Null 

AND WP.tp_BaseViewID Is Null AND WP.tp_DisplayName Is Null 

AND WP.tp_Version Is Null AND WP.tp_WebPartTypeId ='webPartId';

-- Generate WebPart ID  from web part assembly name & web part type

string data = assemblyName + "|" + typeName;

byte[] dataBytes = Encoding.Unicode.GetBytes(data);

MD5 hashAlgo = new MD5CryptoServiceProvider();

byte[] hash = hashAlgo.ComputeHash(dataBytes);

Guid hashGuid = new Guid(hash);

WebPartId = hashGuid.ToString();

Get Content Databases  from Farm:

SPFarm thisFarm = SPFarm.Local;

SPWebService contentService = (SPWebService)thisFarm.Services.GetValue<SPWebService>("");

foreach (SPWebApplication webApp in contentService.WebApplications)

{

string webAppUrl = webApp.GetResponseUri(SPUrlZone.Default).AbsoluteUri;

   foreach (SPContentDatabase contentDb in webApp.ContentDatabases)

   {

      string server = contentDb.Server;

      string dbName = contentDb.Name;                

   }               

}