Query all SQL DB sizes

Original source here

with fs
as
(
    select database_id, type, size * 8.0 / 1000 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

However not all users have these permissions, script below to check a single table across multiple dbs.




This includes check whether the table exists

Declare @SQL VARCHAR(Max) ='use [?];
If Exists(Select P.name FROM sys.tables P Where P.name=''' + 'tablename' + ''')
begin
Insert #Tablecounts
        ( Counting, Databasename )
Select COUNT(1),DB_NAME()
From tablenamewith (nolock)
end'

Print @SQL

CREATE --drop --alter 
TABLE #Tablecounts
(Counting BIGINT
,Databasename VARCHAR(200)
)


Exec sp_MSforeachdb @SQL

SELECT * FROM #Tablecounts

Drop Table #Tablecounts