SharePoint SQL Query to Get the Site Names, Site List & Libraries
SELECT
Webs.Title AS 'Site Name',
AllLists.tp_Title AS 'List Name',
AllDocs.DirName AS 'List Partial URL',
count(1) as 'Record Count'
FROM AllDocs
JOIN AllLists
ON AllLists.tp_ID = AllDocs.ListId
JOIN Webs
ON Webs.Id = AllLists.tp_WebId
WHERE
Webs.FullUrl like 'client/%'
group by Webs.Title,AllLists.tp_Title, AllDocs.DirName
ORDER BY Webs.Title
To see the actual record we need to run the following query
SELECT Webs.Title AS 'Site Name',
AllLists.tp_Title AS 'List Name',
AllDocs.DirName AS 'List Partial URL',
AllDocs.LeafName AS 'File Name',
AllDocs.ExtensionForFile AS 'Type',
AllDocs.TimeCreated AS 'Date Created'
FROM AllDocs
JOIN AllLists
ON AllLists.tp_ID = AllDocs.ListId
JOIN Webs
ON Webs.Id = AllLists.tp_WebId
WHERE
Webs.FullUrl like 'client/a%'
ORDER BY Webs.Title