Dynamic SQL: String truncation in EXECUTE-Statment

Question

I wanted to monitor sizes of any databases in an SQL Server instance so I came up with this query that builds a temporary table which delivers what I want

/*DROP TEMP TABLE IF STILL EXISTENT*/
IF OBJECT_ID('tempdb..#sizes') IS NOT NULL
DROP TABLE #sizes;
/*DECLARE CURSOR curs1 to query the names of all relevant databases*/
DECLARE curs1 INSENSITIVE CURSOR FOR
select CAST(name AS varchar(MAX)) from sys.databases WHERE state_desc ='ONLINE' AND name NOT IN ('tempdb', 'master', 'model', 'msdb', 'sysdb', 'tempdb2')
/*Open Cursor*/
OPEN curs1
/*Declare Variable to hold the value of current db*/
declare @dbname varchar(MAX) = 'dummy'
/*Create the temporary table for results*/
CREATE TABLE #sizes (DBname varchar(MAX), physicalname varchar(MAX), TotalSizeMB INT, AvailableSpaceMB INT)
/*Query the datafile statistics for every database dynamically*/
WHILE(1=1)
BEGIN
FETCH NEXT FROM curs1 INTO @dbname
IF @@FETCH_STATUS != 0
BREAK;
EXECUTE('USE ' + @dbname + ';INSERT INTO #sizes
SELECT DB_NAME(), f.physical_name,
CAST((f.size/128.0) AS DECIMAL(15,2)), 
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(15,2))
FROM sys.database_files f;')
END
Select * from #sizes
/*Cleanup*/
DROP TABLE #sizes

CLOSE curs1
DEALLOCATE curs1

When the cursor fetches a string containing a "-", it splits the string at that point, leaving an incomplete database name, which can not be found. I isolated the EXECUTE function to be the problem. I assume this has to do with the change of of context when changing the database with the use command. But I also tried the datatypes nvarchar, text and ntext (which are not valid for local variables), char and nchar as well as sysname, which is the fieldtype of the sys.databases' "name"-column. Any suggestions on how to stop this behaviour?

You don't need a cursor here. I really dislike cursors even for administrative tasks like this. I will post a simpler approach to this shortly. — Sean Lange
– Sean Lange, Commented Sep 28, 2017 at 15:01

Peter B · Accepted Answer · 2017-09-28 14:49:31Z

2

Try escaping the database name:

'USE [' + @dbname + ']; ...

answered Sep 28, 2017 at 14:49

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean Lange · Accepted Answer · 2017-09-28 15:28:37Z

0

Here is a quick example of how you can do this without using a cursor. It still requires dynamic sql but no cursor. :) I realize you are using FILEPROPERTY which will be NULL here but I tossed this example together quickly. And since I suspect you will probably continue to use the cursor version you already have I didn't refactor that piece.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 
'SELECT DatabaseName = ''' + name + ''', f.physical_name collate SQL_Latin1_General_CP1_CI_AS,
CAST((f.size/128.0) AS DECIMAL(15,2)), 
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(15,2))
FROM [' + name + '].sys.database_files f union all '
from sys.databases 
WHERE state_desc ='ONLINE' 
    AND name NOT IN ('tempdb', 'master', 'model', 'msdb', 'sysdb', 'tempdb2')

select @SQL = left(@SQL, len(@SQL) - 10)

select @SQL
--Uncomment the next line when you are comfortable the dynamic sql will work
--exec sp_executesql @SQL

answered Sep 28, 2017 at 15:28

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

4 Comments

Laghorn Over a year ago

Thank you! That is very interesting. Especially since I am having trouble fetching the results of my solution in php5.

Sean Lange Over a year ago

Much simpler than a cursor. :)

Laghorn Over a year ago

Could you please elaborate on why the FILEPROPERTY-expression results in a NULL-value? The filename parameter expects nchar(128) data, but even when I use CAST, it does not work.

Sean Lange Over a year ago

FILEPROPERTY is a function that is relative to the database it is currently executing on. learn.microsoft.com/en-us/sql/t-sql/functions/… And in the code I put here the entire thing is run against one database. Thus FILEPROPERTY will return NULL because that file does not exist for the current database. Look closely and you will find that it returns values only for the database where you run this.

Collectives™ on Stack Overflow

Dynamic SQL: String truncation in EXECUTE-Statment

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related