Size and Name of all Databases

Question

Post reply

Size and Name of all Databases

February 27, 2023 at 8:44 am

This was removed by the editor as SPAM

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

tapankumar.bhatt-1037817 Grasshopper Points: 18 More actions · Answer 1

Hi All,

Please find query. I made some customization in the Query developed by central user.

Create TABLE #db_file_information(

fileid integer

, theFileGroup integer

, Total_Extents integer

, Used_Extents integer

, db varchar(30)

, file_Path_name varchar(300))

-- Get the size of the datafiles

insert into #db_file_information

( fileid

, theFileGroup

, Total_Extents

, Used_Extents

, db

, file_Path_name )

exec sp_MSForEachDB 'Use ?; DBCC showfilestats'

-- add two columns to the temp table

alter table #db_file_information add PercentFree as

((Total_Extents-Used_Extents)*100/(Total_extents))

alter table #db_file_information add TotalSpace_MB as

((Total_Extents*64)/1024)

alter table #db_file_information add UsedSpace_MB as

((Used_Extents*64)/1024)

alter table #db_file_information add FreeSpace_MB as

((Total_Extents*64)/1024-(Used_Extents*64)/1024)

--select db,sum(total_extents) , sum(used_extents) from #db_file_information group by db

select b.dbid,c.name,sum(a.totalspace_mb) as "Total Size" ,sum(a.usedspace_mb) as "Used Space" ,

sum(a.Freespace_MB) as "Free Size" from #db_file_information as a , sys.sysaltfiles as b, sys.sysdatabases as c

where b.filename=a.file_path_name and b.dbid=c.dbid and c.filename=a.file_path_name

group by b.dbid,c.name

drop table #db_file_information

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

tapankumar.bhatt-1037817 (3/23/2010)
Hi All,
Please find query. I made some customization in the Query developed by central user.
Create TABLE #db_file_information(
fileid integer
, theFileGroup integer
, Total_Extents integer
, Used_Extents integer
, db varchar(30)
, file_Path_name varchar(300))
-- Get the size of the datafiles
insert into #db_file_information
( fileid
, theFileGroup
, Total_Extents
, Used_Extents
, db
, file_Path_name )
exec sp_MSForEachDB 'Use ?; DBCC showfilestats'
-- add two columns to the temp table
alter table #db_file_information add PercentFree as
((Total_Extents-Used_Extents)*100/(Total_extents))
alter table #db_file_information add TotalSpace_MB as
((Total_Extents*64)/1024)
alter table #db_file_information add UsedSpace_MB as
((Used_Extents*64)/1024)
alter table #db_file_information add FreeSpace_MB as
((Total_Extents*64)/1024-(Used_Extents*64)/1024)
--select db,sum(total_extents) , sum(used_extents) from #db_file_information group by db
select b.dbid,c.name,sum(a.totalspace_mb) as "Total Size" ,sum(a.usedspace_mb) as "Used Space" ,
sum(a.Freespace_MB) as "Free Size" from #db_file_information as a , sys.sysaltfiles as b, sys.sysdatabases as c
where b.filename=a.file_path_name and b.dbid=c.dbid and c.filename=a.file_path_name
group by b.dbid,c.name
drop table #db_file_information

I have used a script similar to this in the past with great success.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

subhrajit46 SSCommitted Points: 1595 More actions · Answer 3

Another way...you can run system procedure xp_helpdb to get all the db sizes in an instance.

tapankumar.bhatt-1037817 Grasshopper Points: 18 More actions · Answer 4

Yes that is true. But the SQL i made will give database name, ID with database size used and total. Generally system is producing data file wise results by system procedures.

Anshu Dev SSC Enthusiast Points: 127 More actions · Answer 5

Hi All,

Actually I have got a task to match all the tables of 2 servers (QA and PROD) and add tables to which

ever database doesn't have one or is missing... inshort both databases should have the same tables. Its a huge DB with thousand's of table.

How I am planning to move them is by making a SSIS but I want to know that IS there a way how I can copy all tables names to excel or flat file so that I can compare and match the table names in excel if they are present or not. I dont have access to fire a query on sys files 🙁

Any other way how I can retrieve all the table names from the database.

I would appreciate your help 🙂

Thanks

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

adev 1388 (9/27/2010)
Hi All,
Actually I have got a task to match all the tables of 2 servers (QA and PROD) and add tables to which
ever database doesn't have one or is missing... inshort both databases should have the same tables. Its a huge DB with thousand's of table.
How I am planning to move them is by making a SSIS but I want to know that IS there a way how I can copy all tables names to excel or flat file so that I can compare and match the table names in excel if they are present or not. I dont have access to fire a query on sys files 🙁
Any other way how I can retrieve all the table names from the database.
I would appreciate your help 🙂
Thanks

You should post you question on a new topic, instead of hijacking this one, especially since it has nothing to do with the original topic.

Manish_ Ten Centuries Points: 1070 More actions · Answer 7

Hi Michael Valentine Jones,

Just execute this following TSQL in query analyzer in your PROD and QA, and you will get the list of all the tables of that databse

select name from sys.tables

you can copy the results in to excel/notepad and compare.

Thanks

Manish

- Manish

Rich Mechaber SSChampion Points: 11130 More actions · Answer 8

Hmmmmmmm...... did you search this site? When I looked up my script to do this, the first thing in it was this comment:

-- FROM http://www.sqlservercentral.com/Forums/Topic853747-146-1.aspx

😉

Rich