Sql-Server

將臨時表作為參數傳遞給儲存過程的替代方法

  • January 23, 2019

前段時間我正在開發一個簡單的程序來獲取特定伺服器上的數據庫大小。這是程序：

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

以下是我過去呼叫此過程的方式：

   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

在此處輸入圖像描述

問題：

雖然它有效，但是否有更多elegant方法可以通過做其他事情而不是將臨時表名稱作為參數傳遞來完成這項工作？

我需要在呼叫儲存過程後臨時表可用。

在儲存過程中，我需要將數據插入到該臨時表中。

有沒有其他方法可以做到這一點，而不像我一樣使用臨時表作為參數？

這裡有一個類似的問題。

一些連結：

–如何恢復數據庫，忽略每個文件中的可用空間？

–生成 INSERT 列表，但僅適用於不可為空的列

– 確定 SQL Server 數據庫使用的驅動器號

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

– 過程 sp_foreachdb

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

您可以在 sp_getDbSpace 中創建臨時表，然後返回單個結果集。

像這樣：

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

引用自：https://dba.stackexchange.com/questions/227788

相關問答

Postgresql

PostgresSQL 使用數組中的參數值動態執行

  • January 29, 2022
檢視問答
Sql-Server

動態 SQL 中斷語句

  • December 27, 2021
檢視問答
Sql-Server

處理儲存過程中的可選參數

  • July 24, 2021
檢視問答
Sql-Server

SQL SERVER 動態查詢與空值的 Like 語句

  • July 3, 2021
檢視問答
Sql-Server

為什麼要避免在儲存過程中使用動態 SQL？

  • June 4, 2021
檢視問答
Sql-Server

為什麼 TVP 必須是 READONLY，其他類型的參數為什麼不能是 READONLY

  • March 17, 2021
檢視問答