T-SQL modification

Hi Team,

How to modify below t-sql to run on all databases in sql server 2016?
select
        DBName,
        name,
        [filename],
        size as 'Size(MB)',
        usedspace as 'UsedSpace(MB)',
        (size - usedspace) as 'AvailableFreeSpace(MB)'
from       
(   
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

Open in new window

VijayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
pcelbaCommented:
Do you need sys.filegroups in the query? Do you have admin rights?
select
        DBName,
        name,
        [filename],
        size as 'Size(MB)',
        usedspace as 'UsedSpace(MB)',
        (size - usedspace) as 'AvailableFreeSpace(MB)'
from       
(   
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM sys.master_files AS s 
  WHERE ((s.type = 2 or s.type = 0) and (s.drop_lsn IS NULL))
) DBFileSizeInfo

Open in new window

0
 
VijayAuthor Commented:
Yes. I having admin rights.
0
 
pcelbaCommented:
OK, the above query does not provide complete info for all databases. You may look here for much better script.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
There is a beautiful script written by Microsoft for both DATA AND LOG FILES. Please use that-

FOR DATA FILES
------------------------------Data file size---------------------------- 
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
drop table #dbsize 
create table #dbsize 
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
go 
  
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
sum(size)/128.0 AS File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
from sys.database_files  where type=0 group by type' 

SELECT * FROM #dbsize

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/

(10 row(s) affected)
Dbname                           dbstatus       Recovery_Model                           file_Size_MB                            Space_Used_MB                           Free_Space_MB
-------------------------------- -------------- ---------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
master                           ONLINE         SIMPLE                                   8.00                                    4.13                                    3.88
tempdb                           ONLINE         SIMPLE                                   64.00                                   4.69                                    59.31
model                            ONLINE         FULL                                     8.00                                    2.31                                    5.69
msdb                             ONLINE         SIMPLE                                   14.81                                   14.00                                   0.81
ReportServer$MSSQL2016           ONLINE         FULL                                     8.00                                    6.50                                    1.50
ReportServer$MSSQL2016TempDB     ONLINE         SIMPLE                                   8.00                                    3.69                                    4.31
DWDiagnostics                    ONLINE         SIMPLE                                   1000.00                                 84.75                                   915.25
DWConfiguration                  ONLINE         FULL                                     8.00                                    4.06                                    3.94
DWQueue                          ONLINE         SIMPLE                                   8.00                                    2.81                                    5.19
Pawan                            ONLINE         FULL                                     8.00                                    3.13                                    4.88

(10 row(s) affected)

Open in new window


FOR LOG FILE
-------------------log size-------------------------------------- 
  if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 
drop table #logsize 
create table #logsize 
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 
go 
  
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
sum(size)/128.0 AS Log_File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
from sys.database_files  where type=1 group by type' 
  
SELECT * FROM   #logsize

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/

(10 row(s) affected)
Dbname                                  Log_File_Size_MB                        log_Space_Used_MB                       log_Free_Space_MB
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
master                                  5.06                                    1.60                                    3.46
tempdb                                  8.00                                    0.45                                    7.55
model                                   8.00                                    0.59                                    7.41
msdb                                    1.00                                    0.50                                    0.50
ReportServer$MSSQL2016                  8.00                                    1.80                                    6.20
ReportServer$MSSQL2016TempDB            8.00                                    1.80                                    6.20
DWDiagnostics                           72.00                                   9.38                                    62.63
DWConfiguration                         8.00                                    1.27                                    6.73
DWQueue                                 8.00                                    0.94                                    7.06
Pawan                                   8.00                                    1.31                                    6.69

(10 row(s) affected)

Open in new window

https://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use the sp_MSforeachdb undocumented stored procedure. It useful to run a command in all databases.
I've adapted it to use with your script:
DECLARE @MyCommand VARCHAR(MAX) = '
	select
		''?'' DBName,
		name,
		[filename],
		size as ''Size(MB)'',
		usedspace as ''UsedSpace(MB)'',
		(size - usedspace) as ''AvailableFreeSpace(MB)''
	from       
	(   
	SELECT
	s.name AS [Name],
	s.physical_name AS [FileName],
	(s.size * CONVERT(float,8))/1024 AS [Size],
	(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
	s.file_id AS [ID]
	FROM sys.filegroups AS g
	INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id(''?'') and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
	) DBFileSizeInfo'

EXEC sp_MSforeachdb @MyCommand

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Yep, use the undocumented stored procedure  sp_MSforeachdb

It will automatically swap the '?' for the database.

it is important to include 'use [?];' as your first line. There are some things that need just the current DB for granularity, so best to set your db.

Both pawan and vitor have supplied code, so no need to repost. Just include the 'use [?];' as the first line in vitor's example.
0
 
pcelbaCommented:
Mark, if you would read all comments more carefully then you could realize my post also contains a link to code using sp_MSforeachdb call :-).

So to summarize: We have 4 code samples, two of them (ID: 42391577, ID: 42392179) working just partially, the other two (ID: 42391608, ID: 42391648) providing correct results.

To publish the URL seems to me more convenient for the original author than to copy some code without the original source URL publishing. Web page value highly depends on external links...
0
 
Mark WillsTopic AdvisorCommented:
Apologies pcelba,

Didnt see the link, and going back to inspect, now I do.

Sometimes masking the link with 'here' can be easy to miss :)
0
 
pcelbaCommented:
Hidden things are the best ones obviously :-)  

This could work:  ►here
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just include the 'use [?];' as the first line in vitor's example.
There's no need to add the USE statement. The SP will do that automatically.
0
 
VijayAuthor Commented:
Hi pcelba,

Your query is working fine as i expected. But only issue is showing NULL values in UsedSpace and AvailableFreespace Column, except for master database.
0
 
pcelbaCommented:
Yes, that's mentioned as  "ID: 42391577, ID: 42392179 working just partially"  which means NULLs for not current DBs caused by FILEPROPERTY() function call.

The code provided (or referenced) in  ID: 42391608, and ID: 42391648) is providing correct results.

Vitor, IMO, The  'use [?];' must be in the command.
1

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Pawan KumarDatabase ExpertCommented:
Have u tried my comment?
0
 
VijayAuthor Commented:
I found some other T-SQL. which is working as i expected,
Thank you for all.
0
 
VijayAuthor Commented:
Thank you victor
0
 
Mark WillsTopic AdvisorCommented:
Ummm...that's not an answer to this question (ID: 42393117)

And YES it is important to include 'use [?];' as your first line whenever using sp_MSforeachdb

I repeat... There are some things that need just the current DB for granularity, so always best to set your db.

Using Vitor's code (ID: 42392179), you get NULL values as pointed out by pcelba
0
 
VijayAuthor Commented:
This is the script i used.

-- =============================================
-- Author:		Jonathan Roberts
-- Create date: 2015-07-23
-- Description:	Script to display the database data and log file sizes 
--              and available space on the volume.
--              Can be run on a registered server group to get data for all database servers
-- =============================================
GO

DECLARE @ServerVersion varchar(100)
SET @ServerVersion = CONVERT(varchar,SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion, 4)-1)
--PRINT @ServerVersion
DECLARE @command nvarchar(2000)  
    
IF OBJECT_ID('tempdb..#FileData','U') IS NOT NULL
BEGIN
    PRINT 'Dropping #FileData'
    DROP TABLE tempdb..#FileData
END    

CREATE TABLE tempdb..#FileData
(
    [CurrentHost]                   varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [ClusterNodes]                  varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [DB]                            varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [FileType]                      varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [Name]                          varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [VolumeOrDrive]                 varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [FileName]                      varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [File Size (MB)]                decimal(15,2) NULL,
    [Space Used In File (MB)]       decimal(15,2) NULL,
    [Available Space In File (MB)]  decimal(15,2) NULL,
    [Drive Free Space (MB)]         decimal(15,2) NULL
)    
IF CONVERT(float, @ServerVersion) < 10.5 BEGIN --–2000, 2005, 2008

    IF OBJECT_ID('tempdb..#xp_fixeddrives','U') IS NOT NULL
    BEGIN 
        PRINT 'Dropping table #xp_fixeddrives'
        DROP TABLE #xp_fixeddrives;
    END

    CREATE TABLE #xp_fixeddrives
    (
        Drive   varchar(250),
        MBFree  int
    )
    
    INSERT INTO #xp_fixeddrives
    (
        Drive,
        MBFree
    )
    EXEC master..xp_fixeddrives  


    SET @command = '
    USE [?]
    INSERT INTO #FileData
    (
        [CurrentHost],
        [ClusterNodes],
        [DB],
        [FileType],
        [Name],
        [VolumeOrDrive],
        [FileName],
        [File Size (MB)],
        [Space Used In File (MB)],
        [Available Space In File (MB)],
        [Drive Free Space (MB)]
    )
    SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS  [CurrentHost],
           CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
           CONVERT(varchar(250), DB_NAME())             COLLATE Latin1_General_CI_AS    [DB],
           CONVERT(varchar(250), df.type_desc)          COLLATE Latin1_General_CI_AS    [FileType],
           CONVERT(varchar(250), f.Name)                COLLATE Latin1_General_CI_AS    [Name],
           CONVERT(varchar(250), LEFT(f.FileName, 3))   COLLATE Latin1_General_CI_AS    [VolumeOrDrive],
           CONVERT(varchar(250), f.FileName)            COLLATE Latin1_General_CI_AS    [FileName],
           CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2))                             [File Size (MB)],
           CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2))  [Space Used In File (MB)],
           CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2))  [Available Space In File (MB)],
           CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)] 
      FROM dbo.sysfiles f WITH (NOLOCK)
     INNER JOIN sys.database_files df ON df.file_id = f.fileid 
      LEFT JOIN tempdb..#xp_fixeddrives d
             ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'
END
ELSE -- SQL 2008R2+ (function sys.dm_os_volume_stats is available)
BEGIN
    SET @command = 'USE [?]
    INSERT INTO #FileData
    (
        [CurrentHost],
        [ClusterNodes],
        [DB],
        [FileType],
        [Name],
        [VolumeOrDrive],
        [FileName],
        [File Size (MB)],
        [Space Used In File (MB)],
        [Available Space In File (MB)],
        [Drive Free Space (MB)]
    )
    SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
           CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
           CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS       [DB],
           CONVERT(varchar(250), df.type_desc)            COLLATE Latin1_General_CI_AS      [FileType],
           CONVERT(varchar(250), f.name)                 COLLATE Latin1_General_CI_AS       [Name],
           CONVERT(varchar(250), v.volume_mount_point)   COLLATE Latin1_General_CI_AS       [VolumeOrDrive],
           CONVERT(varchar(250), f.[Filename])           COLLATE Latin1_General_CI_AS       [Filename],
           CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2))                                  [File Size (MB)],
           CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2))      [Space Used In File (MB)],
           CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2))    [Available Space In File (MB)],
           CONVERT(Decimal(15,2), v.available_bytes/1048576.0)                              [Drive Free Space (MB)]
      FROM sys.sysfiles f WITH (NOLOCK)
     INNER JOIN sys.database_files df ON df.file_id = f.fileid 
     CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
END -- END IF

EXEC sp_MSforeachdb @command 

SELECT *
  FROM #FileData

DROP TABLE tempdb..#FileData
GO

Open in new window

1
 
VijayAuthor Commented:
Thank you pcelba
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.