1

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure:

USE MASTER
GO
ALTER PROCEDURE dbo.sp_getDBSpace
    @Radhe sysname=null,
    @system_only BIT = NULL ,
    @user_only BIT = NULL ,
    @database_list NVARCHAR(MAX) = NULL ,
    @exclude_list NVARCHAR(MAX) = NULL ,
    @recovery_model_desc NVARCHAR(120) = NULL ,
    @compatibility_level TINYINT = NULL ,
    @state_desc NVARCHAR(120) = N'ONLINE' ,
    @is_read_only BIT = 0 ,
    @is_auto_close_on BIT = NULL ,
    @is_auto_shrink_on BIT = NULL ,
    @is_broker_enabled BIT = NULL 
AS

BEGIN

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SQL NVARCHAR(MAX)


                    SET @sql = N'USE [?]

                    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                    ;with radhe as (

                    SELECT 
                    Servername   = @@servername,
                    DatabaseName = DB_NAME(), 

                    a.FILE_ID, 
                    a.type, -- 0 is log and 1 is data

                    [Drive] = LEFT(UPPER(a.physical_name), 1), 

                    [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), 

                    [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.NAME, ''SpaceUsed'') / 128.000/1024.000, 2)), 

                    [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.NAME, ''SpaceUsed'')) / 128.000/1024.000, 2)), 

                    a.NAME, 
                    a.PHYSICAL_NAME AS [FILENAME]

                    FROM sys.database_files a
                    )

                        insert into ' + @Radhe + N'([ServerName],[DatabaseName],[Drive],[FILE_ID],[FILE_SIZE_GB],[SPACE_USED_GB],[FREE_SPACE_GB],[NAME],[FILENAME],[TOTAL_DATABASE_SIZE])
                        SELECT 
                                             [ServerName]           
                                            ,[DatabaseName]         
                                            ,[Drive]                
                                            ,[FILE_ID]              
                                            ,[FILE_SIZE_GB]         
                                            ,[SPACE_USED_GB]        
                                            ,[FREE_SPACE_GB]        
                                            ,[NAME]                 
                                            ,[FILENAME]    
                                            ,TOTAL_DATABASE_SIZE      = SUM([FILE_SIZE_GB] ) OVER (PARTITION BY A.DATABASENAME )
                                            --,SPACE_USED               = SUM([SPACE_USED_GB] ) OVER (PARTITION BY A.DATABASENAME )
                                            --,TOTAL_FREE_LOG_SPACE_GB  = SUM(CASE WHEN A.TYPE = 0 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
                                            --,TOTAL_FREE_DATA_SPACE_GB = SUM(CASE WHEN A.TYPE = 1 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
                         FROM radhe a


                            '

             exec sp_foreachdb 
                    @system_only = @system_only,
                    @user_only = @user_only,
                    @database_list = @database_list,
                    @exclude_list = @exclude_list,
                    @recovery_model_desc = @recovery_model_desc,
                    @compatibility_level = @compatibility_level,
                    @state_desc = @state_desc,
                    @is_read_only = @is_read_only ,
                    @is_auto_close_on = @is_auto_close_on,
                    @is_auto_shrink_on = @is_auto_shrink_on ,
                    @is_broker_enabled = @is_broker_enabled
                  ,@command=@SQL 

END
GO

And here is how I used to call this procedure:

    IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL 
    DROP TABLE #Radhe
    GO
    CREATE TABLE #Radhe( 
    [ServerName]           NVARCHAR(128)                        NULL,
    [DatabaseName]         NVARCHAR(128)                        NULL,
    [Drive]                NVARCHAR(1)                          NULL,
    [FILE_ID]              INT                                  NOT NULL,
    [FILE_SIZE_GB]         DECIMAL(12,2)                        NULL,
    [SPACE_USED_GB]        DECIMAL(12,2)                        NULL,
    [FREE_SPACE_GB]        DECIMAL(12,2)                        NULL,
    [NAME]                 SYSNAME                              NOT NULL,
    [FILENAME]             NVARCHAR(260)                        NULL,
    [TOTAL_DATABASE_SIZE]  DECIMAL(38,2)                        NULL)

     declare @db_list NVARCHAR(MAX)


     SELECT @db_list  = STUFF((
                                SELECT ', ' + name FROM sys.databases
                                WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB')
                                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

     --just to check the list                                           
     --exec sp_foreachdb @database_list = @db_list
              --       ,@command='use ?; print db_name() + char(13)'   

     exec sp_getDBSpace @Radhe = '#Radhe'
                       ,@database_list = @db_list
                       ,@exclude_list = 'DBA,TABLEBACKUPS,MASTER,MSDB,MODEL,TEMPDB'

     select * from #Radhe

enter image description here

Question:

Although it works is there a more elegant way to get this done by doing something else rather than passing a temp table name as a parameter?

I need the temp table to be available after the call of the stored procedure.

Inside the stored procedure, I need to insert data into that temp table.

is there any other way of getting this done, without using a temp table as a parameter as I did?

there is a similar question here.

Some links:

-- How to restore a database, ignoring the free space within each file?

-- Generate INSERT list but only for non-nullable columns

-- Determining Drive Letters in Use by SQL Server Databases

-- https://www.mssqltips.com/sqlservertip/3048/determining-drive-letters-in-use-by-sql-server-databases/

-- PROCEDURE sp_foreachdb

-- https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_foreachdb.sql

0

You can create the temp table inside sp_getDbSpace and then return a single resultset.

Like this:

CREATE OR ALTER PROCEDURE dbo.sp_getDBSpace
    @Radhe sysname=null,
    @system_only BIT = NULL ,
    @user_only BIT = NULL ,
    @database_list NVARCHAR(MAX) = NULL ,
    @exclude_list NVARCHAR(MAX) = NULL ,
    @recovery_model_desc NVARCHAR(120) = NULL ,
    @compatibility_level TINYINT = NULL ,
    @state_desc NVARCHAR(120) = N'ONLINE' ,
    @is_read_only BIT = 0 ,
    @is_auto_close_on BIT = NULL ,
    @is_auto_shrink_on BIT = NULL ,
    @is_broker_enabled BIT = NULL 
AS

BEGIN

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;

DECLARE @SQL NVARCHAR(MAX)

   CREATE TABLE #Radhe( 
    [ServerName]           NVARCHAR(128)                        NULL,
    [DatabaseName]         NVARCHAR(128)                        NULL,
    [Drive]                NVARCHAR(1)                          NULL,
    [FILE_ID]              INT                                  NOT NULL,
    [FILE_SIZE_GB]         DECIMAL(12,2)                        NULL,
    [SPACE_USED_GB]        DECIMAL(12,2)                        NULL,
    [FREE_SPACE_GB]        DECIMAL(12,2)                        NULL,
    [NAME]                 SYSNAME                              NOT NULL,
    [FILENAME]             NVARCHAR(260)                        NULL,
    [TOTAL_DATABASE_SIZE]  DECIMAL(38,2)                        NULL)


    DECLARE @SQL nvarchar(max) @sql = N'USE [?]

    ;with radhe as (

    SELECT 
    Servername   = @@servername,
    DatabaseName = DB_NAME(), 
    a.FILE_ID, 
    a.type, -- 0 is log and 1 is data
    [Drive] = LEFT(UPPER(a.physical_name), 1), 
    [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), 
    [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.NAME, ''SpaceUsed'') / 128.000/1024.000, 2)), 
    [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.NAME, ''SpaceUsed'')) / 128.000/1024.000, 2)), 
    a.NAME, 
    a.PHYSICAL_NAME AS [FILENAME]

    FROM sys.database_files a
    )

        insert into #Radhe([ServerName],[DatabaseName],[Drive],[FILE_ID],[FILE_SIZE_GB],[SPACE_USED_GB],[FREE_SPACE_GB],[NAME],[FILENAME],[TOTAL_DATABASE_SIZE])
        SELECT 
                                [ServerName]           
                            ,[DatabaseName]         
                            ,[Drive]                
                            ,[FILE_ID]              
                            ,[FILE_SIZE_GB]         
                            ,[SPACE_USED_GB]        
                            ,[FREE_SPACE_GB]        
                            ,[NAME]                 
                            ,[FILENAME]    
                            ,TOTAL_DATABASE_SIZE      = SUM([FILE_SIZE_GB] ) OVER (PARTITION BY A.DATABASENAME )
                            --,SPACE_USED               = SUM([SPACE_USED_GB] ) OVER (PARTITION BY A.DATABASENAME )
                            --,TOTAL_FREE_LOG_SPACE_GB  = SUM(CASE WHEN A.TYPE = 0 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
                            --,TOTAL_FREE_DATA_SPACE_GB = SUM(CASE WHEN A.TYPE = 1 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME )
            FROM radhe a


            '

             exec sp_foreachdb 
                    @system_only = @system_only,
                    @user_only = @user_only,
                    @database_list = @database_list,
                    @exclude_list = @exclude_list,
                    @recovery_model_desc = @recovery_model_desc,
                    @compatibility_level = @compatibility_level,
                    @state_desc = @state_desc,
                    @is_read_only = @is_read_only ,
                    @is_auto_close_on = @is_auto_close_on,
                    @is_auto_shrink_on = @is_auto_shrink_on ,
                    @is_broker_enabled = @is_broker_enabled
                  ,@command=@SQL 

        select * from #Radhe
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.