How to enclose square brackets to a variable which has been set as a value of one variable in T-SQL?

Question

I got a script from the Net that computes the usage of datafiles and transaction log files from a certain SQL Server instance. The script works fine if there is not database name with whitespace or is not too long. However, if the database name has whitespace or is too long, I get the error message "Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'Test'. No entry found with that name. Make sure that the name is entered correctly." In the sample error message, the 'Test' has the full database name of "Test DB" which has a whitespace. Please see the code below:

--Script to calculate information about the Data Files

SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON

DECLARE @dbname varchar(50)
declare @string varchar(250)
set @string = ''

create table #datafilestats
(   Fileid tinyint,
    FileGroup1 tinyint,
    TotalExtents1 dec (8, 2),
    UsedExtents1 dec (8, 2),
    [Name] varchar(50),
    [FileName] sysname )

create table #dbstats
(   dbname varchar(50),
    FileGroupId tinyint,
    FileGroupName varchar(25),
    TotalSizeinMB dec (8, 2),
    UsedSizeinMB dec (8, 2),
    FreeSizeinPercent dec (8, 2))



DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor

FETCH NEXT FROM dbnames_cursor INTO @dbname

WHILE (@@fetch_status = 0)
BEGIN
    set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS'
    insert into #datafilestats  exec (@string)

    insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
    select @dbname, FileGroup1, sum(TotalExtents1)*64.0/1024.0,
sum(UsedExtents1)*64.0/1024.0
    from #datafilestats group by FileGroup1

set @string = 'use ' + @dbname + ' update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' + 
@dbname + ''''

    exec (@string)



    update  #dbstats set FreeSizeinPercent = (TotalSizeinMB - UsedSizeinMB)/TotalSizeinMB*100 where
dbname = @dbname

    truncate table #datafilestats

FETCH NEXT FROM dbnames_cursor INTO @dbname
END

CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor

drop table #datafilestats
select * from #dbstats
drop table #dbstats

--Script to calculate information about the Transaction Log Files


create table #LogUsageInfo
( db_name varchar(50),
  log_size dec (8, 2),
  log_used_percent dec (8, 2),
  status dec (7, 1) )

insert #LogUsageInfo  exec ('dbcc sqlperf(logspace) with no_infomsgs')

select * from #LogUsageInfo

drop table #LogUsageInfo

In my analysis, the error message starts to appear when the line "set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS'" is reached. I did try to execute the commmand

use Test DB
DBCC SHOWFILESTATS

and I got the same error message I mentioned above. But if I do the command

use [Test DB]
DBCC SHOWFILESTATS

it works fine and i don't get the error message.

My question is is there a way in the script to enclose the variable @dbname with square brackets?

Answer 1

yes it is called QuoteName

example

select quotename('bb bb')

Answer 2

Since you are building the string so why not say

set @string = 'use [' + @dbname + '] DBCC SHOWFILESTATS'

Answer 3

Regarding long DB names. It looks like you have to declare @dbname varchar(100) insted of varchar(50) and @string varchar(300) insted of @string varchar(250). And you need similar changes in the table definitions (columns dbname).