none
fileproperty for each database

    Question

  • Hi,

    i want to learn mydatabases file sizes(database file and log file)

    select * from sys.sysfiles gives that,but it is running for only one database.

    i want to learn that for each database,is it possible?

    Thanks

    Wednesday, March 30, 2011 11:20 AM

Answers

  • it's giving that for only one database which is working on.


    To run this script for each database on the server, you'll need to set the database context for each database so that FILEPROPERTY can be used.  Your options are:

    1. Run a script to generate another with a USE statement for each database (Fabrizzio's suggestion)
    2. Run the script using the undocumented sp_MSforeachdb procedure with your script as a parameter (Prasad's suggestion)
    3. Write a script with a cursor over sys.databases and execute your script with a USE statement before each execution:
    DECLARE
    	@SqlStatement nvarchar(MAX)
    	,@DatabaseName sysname;
    	
    IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
    	DROP TABLE #DatabaseSpace;
    	
    CREATE TABLE #DatabaseSpace(
    	DATABASE_NAME	sysname
    	,LOGICAL_NAME	sysname
    	,FILE_SIZE_MB	decimal(12, 2)
    	,SPACE_USED_MB	decimal(12, 2)
    	,FREE_SPACE_MB	decimal(12, 2)
    	,FILE_NAME		sysname
    	);
    	
    DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
    	SELECT name FROM sys.databases;
    	
    OPEN DatabaseList;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    	IF @@FETCH_STATUS = -1 BREAK;
    	SET @SqlStatement = N'USE '
    		+ QUOTENAME(@DatabaseName)
    		+ CHAR(13)+ CHAR(10)
    		+ N'INSERT INTO #DatabaseSpace
    	SELECT
    		[DATABASE_NAME] = DB_NAME()
    		,[LOGICAL_NAME] = f.name
    		,[FILE_SIZE_MB] = CONVERT(decimal(12,2),round(f.size/128.000,2))
    		,[SPACE_USED_MB] = CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))
    		,[FREE_SPACE_MB] = CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))
    		,[FILENAME] = f.name
    	FROM sys.database_files f;';
    
    	EXECUTE(@SqlStatement);
    	
    END
    CLOSE DatabaseList;
    DEALLOCATE DatabaseList;
    
    SELECT * FROM #DatabaseSpace;
    
    DROP TABLE #DatabaseSpace;;
    GO
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by goksuuu Wednesday, March 30, 2011 1:13 PM
    Wednesday, March 30, 2011 12:39 PM

All replies

  • Gok,

     

     

    Try this code:

     

    declare @sql varchar(8000)

    declare @script varchar(8000)

    set @sql='select * from sys.sysfiles'

    set @script=''

    select @script=@script+'use '+name+CHAR(13)+@sql+char(13)+char(13)

    from sys.databases

    EXECUTE (@script)


    ------------------------------------------------------------- Oracle OCA11g
    Wednesday, March 30, 2011 11:26 AM
  • try below statement

    Use Master

    go

    exec

    sp_MSforeachdb @command1 = "select * from sys.sysfiles where name = ?"


    If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
    Wednesday, March 30, 2011 11:38 AM
  • See Peter script

    SELECT      @@SERVERNAME AS SqlServerInstance,
                db.name AS DatabaseName,
                SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
                SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
                SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
    FROM        master..sysdatabases AS db
    INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
    WHERE       db.name NOT IN('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb')      

    GROUP BY    db.name


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, March 30, 2011 11:39 AM
    Answerer
  • Hi,

    i want to learn mydatabases file sizes(database file and log file)

    select * from sys.sysfiles gives that,but it is running for only one database.

    i want to learn that for each database,is it possible?

    Thanks

    thanks for your responses,

    select

    a.name,

    [FILE_SIZE_MB] = 

    convert(decimal(12,2),round(a.size/128.000,2)),

    [SPACE_USED_MB] =

    convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),

    [FREE_SPACE_MB] =

    convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,

    NAME = left(a.NAME,15),

    FILENAME = left(a.name,30)

    from

    sys.master_files a

    this code is working,but i want to learn space_used and free_space.

    it's giving that for only one database which is working on.

    Wednesday, March 30, 2011 11:53 AM
  • it's giving that for only one database which is working on.


    To run this script for each database on the server, you'll need to set the database context for each database so that FILEPROPERTY can be used.  Your options are:

    1. Run a script to generate another with a USE statement for each database (Fabrizzio's suggestion)
    2. Run the script using the undocumented sp_MSforeachdb procedure with your script as a parameter (Prasad's suggestion)
    3. Write a script with a cursor over sys.databases and execute your script with a USE statement before each execution:
    DECLARE
    	@SqlStatement nvarchar(MAX)
    	,@DatabaseName sysname;
    	
    IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
    	DROP TABLE #DatabaseSpace;
    	
    CREATE TABLE #DatabaseSpace(
    	DATABASE_NAME	sysname
    	,LOGICAL_NAME	sysname
    	,FILE_SIZE_MB	decimal(12, 2)
    	,SPACE_USED_MB	decimal(12, 2)
    	,FREE_SPACE_MB	decimal(12, 2)
    	,FILE_NAME		sysname
    	);
    	
    DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
    	SELECT name FROM sys.databases;
    	
    OPEN DatabaseList;
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    	IF @@FETCH_STATUS = -1 BREAK;
    	SET @SqlStatement = N'USE '
    		+ QUOTENAME(@DatabaseName)
    		+ CHAR(13)+ CHAR(10)
    		+ N'INSERT INTO #DatabaseSpace
    	SELECT
    		[DATABASE_NAME] = DB_NAME()
    		,[LOGICAL_NAME] = f.name
    		,[FILE_SIZE_MB] = CONVERT(decimal(12,2),round(f.size/128.000,2))
    		,[SPACE_USED_MB] = CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))
    		,[FREE_SPACE_MB] = CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))
    		,[FILENAME] = f.name
    	FROM sys.database_files f;';
    
    	EXECUTE(@SqlStatement);
    	
    END
    CLOSE DatabaseList;
    DEALLOCATE DatabaseList;
    
    SELECT * FROM #DatabaseSpace;
    
    DROP TABLE #DatabaseSpace;;
    GO
    

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by goksuuu Wednesday, March 30, 2011 1:13 PM
    Wednesday, March 30, 2011 12:39 PM
  • thank you very much Dan,it's working.
    Wednesday, March 30, 2011 1:15 PM
  • Thanks for posting. This is exactly what I was looking for.
    Friday, August 10, 2012 9:57 AM
  • thank you so much. Your answer is very useful.

    I am a SQL beginner. I would like to ask some questions about add last user update into table like

    LOGICAL_NAME||FILE_SIZE_MB||SPACE_USED_MB||FREE_SPACE_MB||FILE_NAME||last user update

    The selection statement is also created that

    select max(last_user_update) as LastUserUpdate,database_id as [dbid] from sys.dm_db_index_usage_stats 
    group by database_id


    There are grouping by database_id, can I add it into table grouping by database_id?

    Thank you for your help

    Friday, March 27, 2015 9:57 AM
  • Kirame16, I suggest you post a new question rather than continue an old already answered thread.  That will increase the likelihood of an expedient answer.  In your question, provide the DDL (create table statements), sample data, expected results, and the query you've attempted. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, March 30, 2015 12:38 PM