IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Query avec tables temporaires -> tables physique [2008R2]


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre Expert
    Homme Profil pro
    DBA SQL Server
    Inscrit en
    Octobre 2012
    Messages
    862
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : DBA SQL Server
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Octobre 2012
    Messages : 862
    Par défaut Query avec tables temporaires -> tables physique
    Bonjour à tous,

    J'ai trouvé la sp_SDS qui me permet de faire ce que je recherche. Soit avoir une liste de mes DB avec leur accroissement. J'aimerais lancé cette sp tous les jours afin de pouvoir avoir un suivi de l'agrandissement de mes DB quotidiennement.

    Donc, pour transformer les tables temporaires en tables permanente, je retire donc le ## devant les tables.

    Voici l'original

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
    308
    309
    310
    311
    312
    313
    314
    315
    316
    317
    318
    319
    320
    321
    322
    323
    324
    USE _DBA;
    GO
     
    IF OBJECT_ID('dbo.sp_SDS', 'P') IS NOT NULL
      DROP PROCEDURE dbo.sp_SDS;
    GO
     
    CREATE PROCEDURE dbo.sp_SDS 
      @TargetDatabase sysname = NULL,     --  NULL: all dbs
      @Level varchar(10) = 'Database',    --  or "File"
      @UpdateUsage bit = 0,               --  default no update
      @Unit char(2) = 'MB'                --  Megabytes, Kilobytes or Gigabytes
    AS
     
    /**************************************************************************************************
    **
    **  author: Richard Ding
    **  date:   4/8/2008
    **  usage:  list db size AND path w/o SUMmary
    **  test code: sp_SDS   --  default behavior
    **             sp_SDS 'maAster'
    **             sp_SDS NULL, NULL, 0
    **             sp_SDS NULL, 'file', 1, 'GB'
    **             sp_SDS 'Test_snapshot', 'Database', 1
    **             sp_SDS 'Test', 'File', 0, 'kb'
    **             sp_SDS 'pfaids', 'Database', 0, 'gb'
    **             sp_SDS 'tempdb', NULL, 1, 'kb'
    **   
    **************************************************************************************************/
     
    SET NOCOUNT ON;
     
    IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL
      BEGIN
        RAISERROR(15010, -1, -1, @TargetDatabase);
        RETURN (-1)
      END
     
    IF OBJECT_ID('tempdb.dbo.##Tbl_CombinedInfo', 'U') IS NOT NULL
      DROP TABLE dbo.##Tbl_CombinedInfo;
     
    IF OBJECT_ID('tempdb.dbo.##Tbl_DbFileStats', 'U') IS NOT NULL
      DROP TABLE dbo.##Tbl_DbFileStats;
     
    IF OBJECT_ID('tempdb.dbo.##Tbl_ValidDbs', 'U') IS NOT NULL
      DROP TABLE dbo.##Tbl_ValidDbs;
     
    IF OBJECT_ID('tempdb.dbo.##Tbl_Logs', 'U') IS NOT NULL
      DROP TABLE dbo.##Tbl_Logs;
     
    CREATE TABLE dbo.##Tbl_CombinedInfo (
      DatabaseName sysname NULL, 
      [type] VARCHAR(10) NULL, 
      LogicalName sysname NULL,
      T dec(10, 2) NULL,
      U dec(10, 2) NULL,
      [U(%)] dec(5, 2) NULL,
      F dec(10, 2) NULL,
      [F(%)] dec(5, 2) NULL,
      PhysicalName sysname NULL );
     
    CREATE TABLE dbo.##Tbl_DbFileStats (
      Id int identity, 
      DatabaseName sysname NULL, 
      FileId int NULL, 
      FileGroup int NULL, 
      TotalExtents bigint NULL, 
      UsedExtents bigint NULL, 
      Name sysname NULL, 
      FileName varchar(255) NULL );
     
    CREATE TABLE dbo.##Tbl_ValidDbs (
      Id int identity, 
      Dbname sysname NULL );
     
    CREATE TABLE dbo.##Tbl_Logs (
      DatabaseName sysname NULL, 
      LogSize dec (10, 2) NULL, 
      LogSpaceUsedPercent dec (5, 2) NULL,
      Status int NULL );
     
    DECLARE @Ver varchar(10), 
            @DatabaseName sysname, 
            @Ident_last int, 
            @String varchar(2000),
            @BaseString varchar(2000);
     
    SELECT @DatabaseName = '', 
           @Ident_last = 0, 
           @String = '', 
           @Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005' 
                       WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000' 
                       WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008' 
                  END;
     
    SELECT @BaseString = 
    ' SELECT DB_NAME(), ' + 
    CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log''  ELSE ''Data'' END' 
      ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END + 
    ', name, ' + 
    CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END + 
    ', size*8.0/1024.0 FROM ' + 
    CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_files' END + 
    ' WHERE '
    + CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + '';
     
    SELECT @String = 'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM ' + 
                     CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases' 
                          WHEN @Ver IN ('SQL 2005', 'SQL 2008') THEN 'master.sys.databases' 
                     END + ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC';
    EXEC (@String);
     
    INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');
     
    --  For data part
    IF @TargetDatabase IS NOT NULL
      BEGIN
        SELECT @DatabaseName = @TargetDatabase;
        IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE' 
              AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
          BEGIN
            SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)';
            PRINT '*** ' + @String + ' *** ';
            EXEC (@String);
            PRINT '';
          END
     
        SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString; 
     
        INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
              EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
        EXEC ('USE [' + @DatabaseName + '] ' + @String);
     
        UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName; 
      END
    ELSE
      BEGIN
        WHILE 1 = 1
          BEGIN
            SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC;
            IF @@ROWCOUNT = 0
              BREAK;
            IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE' 
                  AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
              BEGIN
                SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') ';
                PRINT '*** ' + @String + '*** ';
                EXEC (@String);
                PRINT '';
              END
     
            SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFileStats;
     
            SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString; 
     
            EXEC ('USE [' + @DatabaseName + '] ' + @String);
     
            INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
              EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
     
            UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY;
          END
      END
     
    --  set used size for data files, do not change total obtained from sys.database_files as it has for log files
    UPDATE dbo.##Tbl_CombinedInfo 
    SET U = s.UsedExtents*8*8/1024.0 
    FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_DbFileStats s 
    ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;
     
    --  set used size and % values for log files:
    UPDATE dbo.##Tbl_CombinedInfo 
    SET [U(%)] = LogSpaceUsedPercent, 
    U = T * LogSpaceUsedPercent/100.0
    FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_Logs l 
    ON l.DatabaseName = t.DatabaseName 
    WHERE t.type = 'Log';
     
    UPDATE dbo.##Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;
     
    UPDATE dbo.##Tbl_CombinedInfo SET [F(%)] = F*100.0/T;
     
    IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'
      BEGIN
        IF @Unit = 'KB'
          UPDATE dbo.##Tbl_CombinedInfo
          SET T = T * 1024, U = U * 1024, F = F * 1024;
     
        IF @Unit = 'GB'
          UPDATE dbo.##Tbl_CombinedInfo
          SET T = T / 1024, U = U / 1024, F = F / 1024;
     
        SELECT DatabaseName AS 'Database',
          type AS 'Type',
          LogicalName,
          T AS 'Total',
          U AS 'Used',
          [U(%)] AS 'Used (%)',
          F AS 'Free',
          [F(%)] AS 'Free (%)',
          PhysicalName
          FROM dbo.##Tbl_CombinedInfo 
          WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') 
          ORDER BY DatabaseName ASC, type ASC;
     
        SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
            SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.##Tbl_CombinedInfo;
      END
     
    IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE'
      BEGIN
        DECLARE @Tbl_Final TABLE (
          DatabaseName sysname NULL,
          TOTAL dec (10, 2),
          [=] char(1),
          used dec (10, 2),
          [used (%)] dec (5, 2),
          [+] char(1),
          free dec (10, 2),
          [free (%)] dec (5, 2),
          [==] char(2),
          Data dec (10, 2),
          Data_Used dec (10, 2),
          [Data_Used (%)] dec (5, 2),
          Data_Free dec (10, 2),
          [Data_Free (%)] dec (5, 2),
          [++] char(2),
          Log dec (10, 2),
          Log_Used dec (10, 2),
          [Log_Used (%)] dec (5, 2),
          Log_Free dec (10, 2),
          [Log_Free (%)] dec (5, 2) );
     
        INSERT INTO @Tbl_Final
          SELECT x.DatabaseName, 
               x.Data + y.Log AS 'TOTAL', 
               '=' AS '=', 
               x.Data_Used + y.Log_Used AS 'U',
               (x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log)  AS 'U(%)',
               '+' AS '+',
               x.Data_Free + y.Log_Free AS 'F',
               (x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log)  AS 'F(%)',
               '==' AS '==',
               x.Data, 
               x.Data_Used, 
               x.Data_Used*100/x.Data AS 'D_U(%)',
               x.Data_Free, 
               x.Data_Free*100/x.Data AS 'D_F(%)',
               '++' AS '++', 
               y.Log, 
               y.Log_Used, 
               y.Log_Used*100/y.Log AS 'L_U(%)',
               y.Log_Free, 
               y.Log_Free*100/y.Log AS 'L_F(%)'
          FROM 
          ( SELECT d.DatabaseName, 
                   SUM(d.T) AS 'Data', 
                   SUM(d.U) AS 'Data_Used', 
                   SUM(d.F) AS 'Data_Free' 
              FROM dbo.##Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x
          JOIN 
          ( SELECT l.DatabaseName, 
                   SUM(l.T) AS 'Log', 
                   SUM(l.U) AS 'Log_Used', 
                   SUM(l.F) AS 'Log_Free' 
              FROM dbo.##Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y
          ON x.DatabaseName = y.DatabaseName;
     
        IF @Unit = 'KB'
          UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024,
          used = used * 1024,
          free = free * 1024,
          Data = Data * 1024,
          Data_Used = Data_Used * 1024,
          Data_Free = Data_Free * 1024,
          Log = Log * 1024,
          Log_Used = Log_Used * 1024,
          Log_Free = Log_Free * 1024;
     
         IF @Unit = 'GB'
          UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024,
          used = used / 1024,
          free = free / 1024,
          Data = Data / 1024,
          Data_Used = Data_Used / 1024,
          Data_Free = Data_Free / 1024,
          Log = Log / 1024,
          Log_Used = Log_Used / 1024,
          Log_Free = Log_Free / 1024;
     
          DECLARE @GrantTotal dec(11, 2);
          SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final;
     
          SELECT 
          CONVERT(dec(10, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)', 
          DatabaseName AS 'DATABASE',
          CONVERT(VARCHAR(12), used) + '  (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED  (%)',
          [+],
          CONVERT(VARCHAR(12), free) + '  (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE  (%)',
          [=],
          TOTAL, 
          [=],
          CONVERT(VARCHAR(12), Data) + '  (' + CONVERT(VARCHAR(12), Data_Used) + ',  ' + 
          CONVERT(VARCHAR(12), [Data_Used (%)]) + '%)' AS 'DATA  (used,  %)',
          [+],
          CONVERT(VARCHAR(12), Log) + '  (' + CONVERT(VARCHAR(12), Log_Used) + ',  ' + 
          CONVERT(VARCHAR(12), [Log_Used (%)]) + '%)' AS 'LOG  (used,  %)'
            FROM @Tbl_Final 
            WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')
            ORDER BY DatabaseName ASC;
     
        IF @TargetDatabase IS NULL
          SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM', 
          SUM (used) AS 'USED', 
          SUM (free) AS 'FREE', 
          SUM (TOTAL) AS 'TOTAL', 
          SUM (Data) AS 'DATA', 
          SUM (Log) AS 'LOG' 
          FROM @Tbl_Final;
      END
     
    RETURN (0)
     
    GO
    Et voici sans les ##

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
    308
    309
    310
    311
    312
    313
    314
    315
    316
    317
    318
    319
    320
    321
    322
    323
    324
    325
    326
    USE _DBA
    GO
     
    IF OBJECT_ID('dbo.sp_SDS', 'P') IS NOT NULL
      DROP PROCEDURE dbo.sp_SDS; 
    GO 
     
    CREATE PROCEDURE dbo.sp_SDS 
      @TargetDatabase nvarchar(128) = NULL,  --  NULL: all dbs 
      @Level varchar(10) = 'Database',       --  or "File" 
      @UpdateUsage bit = 0,                  --  default no update 
      @Unit char(2) = 'MB'                   --  Megabytes, Kilobytes or Gigabytes 
    AS
     
    /************************************************************************************************** 
    ** 
    **  author: Richard Ding 
    **  date:   4/8/2008 
    **  usage:  list db size AND path w/o SUMmary 
    **  test code: sp_SDS   --  default behavior 
    **             sp_SDS 'Master'
    **             sp_SDS NULL, NULL, 0 
    **             sp_SDS NULL, 'file', 1, 'GB'
    **             sp_SDS 'Test_snapshot', 'Database', 1 
    **             sp_SDS 'Test', 'File', 0, 'kb'
    **             sp_SDS 'pfaids', 'Database', 0, 'gb'
    **             sp_SDS 'tempdb', NULL, 1, 'kb'
    ** 
    **************************************************************************************************/ 
     
    SET NOCOUNT ON; 
     
    IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL
      BEGIN
        RAISERROR(15010, -1, -1, @TargetDatabase); 
        RETURN (-1) 
      END
     
    IF OBJECT_ID('tempdb.dbo.Tbl_CombinedInfo', 'U') IS NOT NULL
      DROP TABLE dbo.Tbl_CombinedInfo; 
     
    IF OBJECT_ID('tempdb.dbo.Tbl_DbFileStats', 'U') IS NOT NULL
      DROP TABLE dbo.Tbl_DbFileStats; 
     
    IF OBJECT_ID('tempdb.dbo.Tbl_ValidDbs', 'U') IS NOT NULL
      DROP TABLE dbo.Tbl_ValidDbs; 
     
    IF OBJECT_ID('tempdb.dbo.Tbl_Logs', 'U') IS NOT NULL
      DROP TABLE dbo.Tbl_Logs; 
     
    CREATE TABLE dbo.Tbl_CombinedInfo ( 
      DatabaseName sysname NULL, 
      [type] VARCHAR(10) NULL, 
      LogicalName sysname NULL, 
      T dec(10, 2) NULL, 
      U dec(10, 2) NULL, 
      [U(%)] dec(5, 2) NULL, 
      F dec(10, 2) NULL, 
      [F(%)] dec(5, 2) NULL, 
      PhysicalName nvarchar(255) NULL ); 
     
    CREATE TABLE dbo.Tbl_DbFileStats ( 
      Id int identity, 
      DatabaseName sysname NULL, 
      FileId int NULL, 
      FileGroup int NULL, 
      TotalExtents bigint NULL, 
      UsedExtents bigint NULL, 
      Name sysname NULL, 
      FileName nvarchar(255) NULL ); 
     
    CREATE TABLE dbo.Tbl_ValidDbs ( 
      Id int identity, 
      Dbname sysname NULL ); 
     
    CREATE TABLE dbo.Tbl_Logs ( 
      DatabaseName sysname NULL, 
      LogSize dec (10, 2) NULL, 
      LogSpaceUsedPercent dec (5, 2) NULL, 
      Status int NULL ); 
     
    DECLARE @Ver varchar(20), 
            @DatabaseName sysname, 
            @Ident_last int, 
            @String varchar(2000), 
            @BaseString varchar(2000); 
     
    SELECT @DatabaseName = '', 
           @Ident_last = 0, 
           @String = '', 
           @Ver = CASE WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000'
    		   WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005'
                       WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008'
                       WHEN @@VERSION LIKE '%10.5%' THEN 'SQL 2008 R2'
                       WHEN @@VERSION LIKE '%11.0%' THEN 'SQL 2012'
                  END; 
     
    SELECT @BaseString = 
    ' SELECT DB_NAME(), ' + 
    CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log''  ELSE ''Data'' END'
      ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END + 
    ', name, ' + 
    CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END + 
    ', size*8.0/1024.0 FROM ' + 
    CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_files' END + 
    ' WHERE '
    + CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + ''; 
     
    SELECT @String = 'INSERT INTO dbo.Tbl_ValidDbs SELECT name FROM ' + 
                     CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases'
                          WHEN @Ver IN ('SQL 2005', 'SQL 2008', 'SQL 2008 R2', 'SQL 2012') THEN 'master.sys.databases'
                     END + ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC'; 
    EXEC (@String); 
     
    INSERT INTO dbo.Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'); 
     
    --  For data part 
    IF @TargetDatabase IS NOT NULL
      BEGIN
        SELECT @DatabaseName = @TargetDatabase; 
        IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE'
              AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
          BEGIN
            SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)'; 
            PRINT '*** ' + @String + ' *** '; 
            EXEC (@String); 
            PRINT ''; 
          END
     
        SELECT @String = 'INSERT INTO dbo.Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;  
     
        INSERT INTO dbo.Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName) 
              EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'); 
        EXEC ('USE [' + @DatabaseName + '] ' + @String); 
     
        UPDATE dbo.Tbl_DbFileStats SET DatabaseName = @DatabaseName; 
      END
    ELSE
      BEGIN
        WHILE 1 = 1 
          BEGIN
            SELECT TOP 1 @DatabaseName = Dbname FROM dbo.Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC; 
            IF @@ROWCOUNT = 0 
              BREAK; 
            IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE'
                  AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
              BEGIN
                SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') '; 
                PRINT '*** ' + @String + '*** '; 
                EXEC (@String); 
                PRINT ''; 
              END
     
            SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.Tbl_DbFileStats; 
     
            SELECT @String = 'INSERT INTO dbo.Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;  
     
            EXEC ('USE [' + @DatabaseName + '] ' + @String); 
     
            INSERT INTO dbo.Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName) 
              EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'); 
     
            UPDATE dbo.Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY; 
          END
      END
     
    --  set used size for data files, do not change total obtained from sys.database_files as it has for log files 
    UPDATE dbo.Tbl_CombinedInfo 
    SET U = s.UsedExtents*8*8/1024.0 
    FROM dbo.Tbl_CombinedInfo t JOIN dbo.Tbl_DbFileStats s 
    ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName; 
     
    --  set used size and % values for log files: 
    UPDATE dbo.Tbl_CombinedInfo 
    SET [U(%)] = LogSpaceUsedPercent, 
    U = T * LogSpaceUsedPercent/100.0 
    FROM dbo.Tbl_CombinedInfo t JOIN dbo.Tbl_Logs l 
    ON l.DatabaseName = t.DatabaseName 
    WHERE t.type = 'Log'; 
     
    UPDATE dbo.Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T; 
     
    UPDATE dbo.Tbl_CombinedInfo SET [F(%)] = F*100.0/T; 
     
    IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'
      BEGIN
        IF @Unit = 'KB'
          UPDATE dbo.Tbl_CombinedInfo 
          SET T = T * 1024, U = U * 1024, F = F * 1024; 
     
        IF @Unit = 'GB'
          UPDATE dbo.Tbl_CombinedInfo 
          SET T = T / 1024, U = U / 1024, F = F / 1024; 
     
        SELECT DatabaseName AS 'Database', 
          type AS 'Type', 
          LogicalName, 
          T AS 'Total', 
          U AS 'Used', 
          [U(%)] AS 'Used (%)', 
          F AS 'Free', 
          [F(%)] AS 'Free (%)', 
          PhysicalName 
          FROM dbo.Tbl_CombinedInfo 
          WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') 
          ORDER BY DatabaseName ASC, type ASC; 
     
        SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM', 
            SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.Tbl_CombinedInfo; 
      END
     
    IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE'
      BEGIN
        DECLARE @Tbl_Final TABLE ( 
          DatabaseName sysname NULL, 
          TOTAL dec (10, 2), 
          [=] char(1), 
          used dec (10, 2), 
          [used (%)] dec (5, 2), 
          [+] char(1), 
          free dec (10, 2), 
          [free (%)] dec (5, 2), 
          [==] char(2), 
          Data dec (10, 2), 
          Data_Used dec (10, 2), 
          [Data_Used (%)] dec (5, 2), 
          Data_Free dec (10, 2), 
          [Data_Free (%)] dec (5, 2), 
          [++] char(2), 
          Log dec (10, 2), 
          Log_Used dec (10, 2), 
          [Log_Used (%)] dec (5, 2), 
          Log_Free dec (10, 2), 
          [Log_Free (%)] dec (5, 2) ); 
     
        INSERT INTO @Tbl_Final 
          SELECT x.DatabaseName, 
               x.Data + y.Log AS 'TOTAL', 
               '=' AS '=', 
               x.Data_Used + y.Log_Used AS 'U', 
               (x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log)  AS 'U(%)', 
               '+' AS '+', 
               x.Data_Free + y.Log_Free AS 'F', 
               (x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log)  AS 'F(%)', 
               '==' AS '==', 
               x.Data, 
               x.Data_Used, 
               x.Data_Used*100/x.Data AS 'D_U(%)', 
               x.Data_Free, 
               x.Data_Free*100/x.Data AS 'D_F(%)', 
               '++' AS '++', 
               y.Log, 
               y.Log_Used, 
               y.Log_Used*100/y.Log AS 'L_U(%)', 
               y.Log_Free, 
               y.Log_Free*100/y.Log AS 'L_F(%)'
          FROM
          ( SELECT d.DatabaseName, 
                   SUM(d.T) AS 'Data', 
                   SUM(d.U) AS 'Data_Used', 
                   SUM(d.F) AS 'Data_Free'
              FROM dbo.Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x 
          JOIN
          ( SELECT l.DatabaseName, 
                   SUM(l.T) AS 'Log', 
                   SUM(l.U) AS 'Log_Used', 
                   SUM(l.F) AS 'Log_Free'
              FROM dbo.Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y 
          ON x.DatabaseName = y.DatabaseName; 
     
        IF @Unit = 'KB'
          UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024, 
          used = used * 1024, 
          free = free * 1024, 
          Data = Data * 1024, 
          Data_Used = Data_Used * 1024, 
          Data_Free = Data_Free * 1024, 
          Log = Log * 1024, 
          Log_Used = Log_Used * 1024, 
          Log_Free = Log_Free * 1024; 
     
         IF @Unit = 'GB'
          UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024, 
          used = used / 1024, 
          free = free / 1024, 
          Data = Data / 1024, 
          Data_Used = Data_Used / 1024, 
          Data_Free = Data_Free / 1024, 
          Log = Log / 1024, 
          Log_Used = Log_Used / 1024, 
          Log_Free = Log_Free / 1024; 
     
          DECLARE @GrantTotal dec(11, 2); 
          SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final; 
     
          SELECT
          CONVERT(dec(10, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)', 
          DatabaseName AS 'DATABASE', 
          CONVERT(VARCHAR(12), used) + '  (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED  (%)', 
          [+], 
          CONVERT(VARCHAR(12), free) + '  (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE  (%)', 
          [=], 
          TOTAL, 
          [=], 
          CONVERT(VARCHAR(12), Data) + '  (' + CONVERT(VARCHAR(12), Data_Used) + ',  ' + 
          CONVERT(VARCHAR(12), [Data_Used (%)]) + '%)' AS 'DATA  (used,  %)', 
          [+], 
          CONVERT(VARCHAR(12), Log) + '  (' + CONVERT(VARCHAR(12), Log_Used) + ',  ' + 
          CONVERT(VARCHAR(12), [Log_Used (%)]) + '%)' AS 'LOG  (used,  %)'
            FROM @Tbl_Final 
            WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%') 
            ORDER BY DatabaseName ASC; 
     
        IF @TargetDatabase IS NULL
          SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM', 
          SUM (used) AS 'USED', 
          SUM (free) AS 'FREE', 
          SUM (TOTAL) AS 'TOTAL', 
          SUM (Data) AS 'DATA', 
          SUM (Log) AS 'LOG'
          FROM @Tbl_Final; 
      END
     
    RETURN (0) 
     
    GO
    Cela fonctionne très bien quand j'utilise le script sans le modifier. Mais quand je lance celle que j'ai modifié, je n'ai que la ligne sur la DB sur laquelle je suis alors qu'il devrait me donner pour toutes les DB, voir printscreen ici : http://www.jppinto.com/2011/12/sp_sd...l-server-2012/

    Et je reçois ce message d'erreur. Pourtant je vois bien que ma table existe.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'dbo.Tbl_CombinedInfo'.
    Avez-vous une idée ? A moins que vous ayez une autre idée ?

    Merci d'avance

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Par défaut
    Bonjour,

    Les table temporaire sont créées dans tempdb.

    La procédure vérifie leur existence grâce à ce code: (avant de les dropper si elles existent)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    IF OBJECT_ID('tempdb.dbo.Tbl_CombinedInfo', 'U') IS NOT NULL
      DROP TABLE dbo.Tbl_CombinedInfo;
    Dans votre procédure modifiée, la table est bien trouvée dans tempdb (car vous avez déjà exécuté la procédure initiale avec tables temporaires).
    Mais ensuite, le DROP essaie de supprimer la table dans votre base courante mais ne la trouve pas.

    essayez comme ceci (sans tempdb):

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    IF OBJECT_ID('dbo.Tbl_CombinedInfo', 'U') IS NOT NULL
      DROP TABLE dbo.Tbl_CombinedInfo;

  3. #3
    Membre Expert
    Homme Profil pro
    DBA SQL Server
    Inscrit en
    Octobre 2012
    Messages
    862
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : DBA SQL Server
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Octobre 2012
    Messages : 862
    Par défaut
    Bien vu pour mon erreur, mais malheureusement ça n'a pas résolu le problème :-(

    Je chercherai encore demain, car quand j'utilise les 4 lignes pour faire un drop de mes 4 tables, ça fonctionne très bien, il supprime bien mes tables. Donc ça doit venir du code en dessous. Bizarre. Peux-être que dans le code, je devrais préciser quelques part ma nouvelle DB, pourtant je ne vois nulle part qu'il me parle encore de tempdb...

  4. #4
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Par défaut
    le reste du script fait un USE sur chaque base avant de lancer les INSERT.

    Avec des tables temporaires, cela fonctionne bien, mais dans votre cas, la table est cherchée dans la base correspondant au USE.

    Il faudrait préfixer le nom de la base pour les INSERT. (noms de tables en trois partie)

  5. #5
    Membre Expert
    Homme Profil pro
    DBA SQL Server
    Inscrit en
    Octobre 2012
    Messages
    862
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : DBA SQL Server
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Octobre 2012
    Messages : 862
    Par défaut
    Super, ça fonctionne.

    J'ai ajouté _DBA_SQLSupport (le nom de ma DB) devant tous les noms de mes tables et ça fonctionne.

    Merci !

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [ORACLE] Copie d'une table dans une table temporaire
    Par david71 dans le forum Langage SQL
    Réponses: 3
    Dernier message: 09/08/2007, 11h23
  2. Query avec un CREATE TEMPORARY TABLE = erreur ?
    Par d3mone dans le forum Bases de données
    Réponses: 2
    Dernier message: 16/05/2007, 19h11
  3. Requete avec table temporaire
    Par Piolet dans le forum MS SQL Server
    Réponses: 7
    Dernier message: 22/08/2006, 17h25
  4. [SQL8] Tables Temporaires avec #
    Par Baquardie dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 26/05/2006, 22h21
  5. Affichage des tables temporaires d'Access avec GetTableNames
    Par Oluha dans le forum Bases de données
    Réponses: 2
    Dernier message: 25/05/2005, 16h23

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo