SQL Query to check for large lists

To check for big lists (lists with itemcount > 1500):


create table ##Largelists
(
[listID] uniqueidentifier NULL,
[siteID] uniqueidentifier null,
[webid] uniqueidentifier null,

[counts] bigint NULL,

[fullurl] nvarchar(255) NULL,

[dirname] nvarchar(255) null,

[tp_title] nvarchar(255) 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 ##largelists'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'select a.siteid, a.webid, a.listid, count(a.id) [counts], c.fullurl, a.dirname, b.tp_title,'

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 alldocs as a with (nolock) inner join'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'alllists as b with (nolock) on a.listid=b.tp_id inner join'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'webs as c with (nolock) on b.tp_webid=c.id'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by a.listid, c.fullurl, b.tp_title, a.siteid, a.webid, a.dirname'

SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING count(a.id) > ''1500'''

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 WebID, SiteID, ListID, tp_Title, DirName, Counts, content_DB, servername from ##largelists order by counts desc

DROP TABLE #DBNamesLL

Drop table ##largelists