SQL QUERY: COUNT THE NUMBER OF UNIQUE MEMBERS OF SHAREPOINT GROUPS:

SQL QUERY: COUNT THE NUMBER OF UNIQUE MEMBERS OF SHAREPOINT GROUPS:


The query checks if the SharePoint specific tables exist in the DB scheme otherwise the DB will be skipped.

No rows returned can also mean that you don’t have more than (in this case) 800 users per group defined – the query as it is only outputs problematic groups with more than 800 members:

DBAs can execute the query a second time and change the value 800 to e.g. 10 to see an output.



CREATE TABLE ##UsersPerGroup

(

[Counts] bigint NULL,

[GroupTitle] nvarchar(255) NULL,

[siteID] uniqueidentifier null,

[servername] nvarchar(100) null,

[content_DB] nvarchar(100) null

)

go

CREATE TABLE #DBNamesLL

(

DatabaseName VARCHAR(800),

RecStatus INT Default 0

)

DECLARE @cmdStr NVARCHAR(2000)

DECLARE @dbName VARCHAR(500)

INSERT INTO #DBNamesLL (DatabaseName)

SELECT [Name] FROM sys.databases where state_desc = 'online' ORDER by [Name] ASC



WHILE EXISTS

(SELECT * FROM #DBNamesLL WHERE RecStatus=0)

BEGIN

SELECT TOP 1 @DbName=DatabaseName

FROM #DBNamesLL

WHERE RecStatus=0

SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##UsersPerGroup'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'SELECT COUNT(m.MemberId) as [Counts], g.Title as [GroupTitle], g.SiteId,'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from GroupMembership m with(nolock)'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'join Groups g with(nolock) on m.GroupId = g.ID and m.SiteId = g.SiteId'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by g.Title, g.SiteId'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING COUNT(m.MemberId) > ''800'''

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'



EXEC sp_executesql @Cmdstr

UPDATE #DBNamesLL

SET RecStatus=1

WHERE RecStatus=0

AND DatabaseName=@DbName

END

select Counts AS 'Count', GroupTitle, servername AS ServerName, content_DB AS ContentDB, siteID AS SiteID from ##UsersPerGroup order by counts desc

DROP TABLE #DBNamesLL

Drop table ##UsersPerGroup