Databases
StackOverflow2013
Postsdbo
POHow to enclose square brackets to a variable which has been set as a value of one variable in T-SQL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to enclose square brackets to a variable which has been set as a value of one variable in T-SQL?
    primarykey
    data
    text
    <p>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:</p> <pre><code>--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 </code></pre> <p>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 </p> <pre><code>use Test DB DBCC SHOWFILESTATS </code></pre> <p>and I got the same error message I mentioned above. But if I do the command</p> <pre><code>use [Test DB] DBCC SHOWFILESTATS </code></pre> <p>it works fine and i don't get the error message.</p> <p>My question is is there a way in the script to enclose the variable @dbname with square brackets?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSQLMenace
    1. UStitanium
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to enclose square brackets to a variable which has been set as a value of one variable in T-SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload