Size and Name of all Databases

Question

Post reply

Size and Name of all Databases

Viewing 8 posts - 16 through 22 (of 22 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: 281324 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: 1585 More actions · Answer 3

subhrajit46

SSCommitted

Points: 1585

March 24, 2010 at 12:39 am

#1138710

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

tapankumar.bhatt-1037817

Grasshopper

Points: 18

March 26, 2010 at 6:16 am

#1140266

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.