-2

In our environment, we have more than 60 databases in the SQL Instance. The developer created the _BKP tables and forgot to drop them, causing the database sizes to grow and get a low disk size.
I want a SQL Code to search the table name containing _BKP across all the databases in the SQL Instance.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Pavan Kumar
  • 11
  • 2
  • Does this answer your question? [Display all the names of databases containing particular table](https://stackoverflow.com/questions/18141547/display-all-the-names-of-databases-containing-particular-table) – Thom A Dec 01 '22 at 10:03
  • No, I am looking for row count and Table size as well. – Pavan Kumar Dec 01 '22 at 10:05
  • [SQL count rows in a table](https://stackoverflow.com/a/28917736/2029983) – Thom A Dec 01 '22 at 10:07
  • As I am looking for across all databases. – Pavan Kumar Dec 01 '22 at 10:11
  • Yes, you would combine the 2 duplicates. Use the solution for going through the databases with the solution for getting row counts from the `sys` objects. – Thom A Dec 01 '22 at 10:13
  • This code will give the information about the objects contains _BKP, I also need the row count and object size in MB's (sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%_BKP''') – Pavan Kumar Dec 01 '22 at 10:22
  • Yes, see the prior linked answer: [SQL count rows in a table](https://stackoverflow.com/a/28917736/2029983); then you just need to alter your SQL appropriately. – Thom A Dec 01 '22 at 10:29
  • I tried to alter it but needed help to generate the correct output. If you have it handy, can you please provide the script? – Pavan Kumar Dec 01 '22 at 10:32
  • Not enough effort by OP to solve their own problem. Question is too broad. This is not a coding service. – Tim Jarosz Dec 01 '22 at 13:25

1 Answers1

0

Rather than using sp_msforeachdb I build my own query here, but the method is still the same; build a query that queries each database and use the solution from the other linked answer (SQL count rows in a table) to get the counts for each table:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter nvarchar(50) = @CRLF + N'UNION ALL' + @CRLF;

SELECT @SQL = STRING_AGG(CONVERT(nvarchar(MAX),N'') +
                         N'SELECT N' + QUOTENAME(d.[name],'''') + N' AS DatabaseName,' + @CRLF + 
                         N'       OBJECT_NAME(p.object_id) AS ObjectName,' + @CRLF + 
                         N'       SUM([rows])' + @CRLF + 
                         N'FROM sys.partitions p' + @CRLF + 
                         N'WHERE OBJECT_NAME(p.object_id) LIKE ''%[_]BKP''' + @CRLF +
                         N'  AND p.index_id in (0,1)' + @CRLF +
                         N'GROUP BY p.object_id',@Delimiter) + N';'
FROM sys.databases d;

--PRINT @SQL; --Your debugging best friend

EXEC sys.sp_executesql @SQL;
Thom A
  • 88,727
  • 11
  • 45
  • 75