SQL DBA - Find the no of databases base, no of tables each database and no of column each table

We got requirement  how many no of database in each instance ,  how many  no of tables each database and how many  no of columns in each table. 

Below queries  useful to get the above information. 

1. Below queries will return with multiple result set  with above information. 

--Number of Databases in the Cluster (Production Only)

SELECT COUNT(*) AS NumberOfDatabases

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' );  -- Adjust this filter based on your naming convention

--SELECT * FROM sys.databases

--2. Number of Tables Within Each Database

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

SELECT ''' + name + ''' AS DatabaseName, COUNT(*) AS TableCount

FROM sys.tables;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;

--3. Number of Columns Within Each Table

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += '

USE [' + name + '];

SELECT ''' + name + ''' AS DatabaseName, 

       t.name AS TableName, 

       COUNT(c.column_id) AS ColumnCount

FROM sys.tables t

JOIN sys.columns c ON t.object_id = c.object_id

GROUP BY t.name;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;

2. Below Query  will give the same information in single result set. 

DECLARE @sql NVARCHAR(MAX) = '';

-- Create temp table to store results

SET @sql = '

IF OBJECT_ID(''tempdb..#DBTableColumnInfo'') IS NOT NULL DROP TABLE #DBTableColumnInfo;

CREATE TABLE #DBTableColumnInfo (

    DatabaseName SYSNAME,

    TableName SYSNAME,

    ColumnCount INT

);';

-- Append dynamic SQL for each database

SELECT @sql += '

USE [' + name + '];

INSERT INTO #DBTableColumnInfo (DatabaseName, TableName, ColumnCount)

SELECT 

    ''' + name + ''' AS DatabaseName,

    t.name AS TableName,

    COUNT(c.column_id) AS ColumnCount

FROM sys.tables t

JOIN sys.columns c ON t.object_id = c.object_id

GROUP BY t.name;

'

FROM sys.databases

WHERE name NOT IN ('master','tempdb','model','msdb','staging' ) AND state_desc = 'ONLINE';

-- Final select

SET @sql += '

SELECT 

    DatabaseName,

    COUNT(DISTINCT TableName) AS TableCount,

    SUM(ColumnCount) AS TotalColumns

FROM #DBTableColumnInfo

GROUP BY DatabaseName;

SELECT * FROM #DBTableColumnInfo;

SELECT COUNT(DISTINCT DatabaseName) AS TotalProductionDatabases FROM #DBTableColumnInfo;

';

EXEC sp_executesql @sql;