0

I have the below code to pull the row and column counts from each table within a database (e.g., db1). But I have several databases (e.g., db1, db2 etc.) , so manually updating the database name in the USE statement for every run isn't very convenient. Is there a way to pass a list of database names in a cursor (or something else that allows iteration) and then run the below query for every database in the list, appending the results from each run? I can get the list of database names from this query select name from master.dbo.sysdatabases where name like '%db%'.

USE [db1]


;with [rowCount] as
(
    SELECT  DB_NAME() as [DB_Name],
            QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],
            SUM(sPTN.Rows) AS [RowCount]
    FROM    SYS.OBJECTS AS sOBJ
    INNER JOIN SYS.PARTITIONS AS sPTN
        ON sOBJ.object_id = sPTN.object_id
    WHERE
        sOBJ.type = 'U'
        AND sOBJ.is_ms_shipped = 0x0
        AND index_id < 2 -- 0:Heap, 1:Clustered
    GROUP BY      
        sOBJ.schema_id
        ,sOBJ.name
)
,columnCount as
(
    select 
        QUOTENAME(col.TABLE_SCHEMA) + '.' + QUOTENAME(col.TABLE_NAME) AS [TableName],
        count(*) as ColumnCount
    from INFORMATION_SCHEMA.COLUMNS col
    inner join INFORMATION_SCHEMA.TABLES tbl
        on col.TABLE_SCHEMA = tbl.TABLE_SCHEMA
        and col.TABLE_NAME = tbl.TABLE_NAME
        and tbl.TABLE_TYPE <> 'view'
    group by 
        QUOTENAME(col.TABLE_SCHEMA) + '.' + QUOTENAME(col.TABLE_NAME)
)
select r.[DB_Name], r.TableName, r.[RowCount], c.ColumnCount
from [rowCount] r
inner join columnCount c
    on r.TableName = c.TableName
ORDER BY r.[TableName]
Thom A
  • 88,727
  • 11
  • 45
  • 75
Chipmunk_da
  • 467
  • 2
  • 9
  • 27
  • Does this answer your question? [How to run the same query on all the databases on an instance?](https://stackoverflow.com/questions/18462410/how-to-run-the-same-query-on-all-the-databases-on-an-instance) – Thom A Dec 08 '22 at 11:48
  • Does this answer your question? [Executing SQL query on multiple databases](https://stackoverflow.com/questions/39336171/executing-sql-query-on-multiple-databases) – Thom A Dec 08 '22 at 11:49
  • @Larnu thank you for the links but neither solution works in my case. The first one runs a query on a specific table only. The second one requires you to specify the name of each database separately and copy-paste your query for each db, which is similar to what I already have. I'll try the second solution in the second solution (from marioosh) - looks promising. – Chipmunk_da Dec 10 '22 at 11:55
  • You say you want to run a *query", so that query can reference multiple tables, it doesn't have to just reference one. The part you need to be interested in is the part that demonstrates running it on each database, not the query inside that, which would be specific to that question. The solution to run the query is the same, you just need to put your query (above) into the dynamic statement. – Thom A Dec 10 '22 at 12:12

0 Answers0