sp_executesql

• kirkysql

  Old Hand

  Points: 350

  More actions

  February 9, 2004 at 10:03 am

  #359099

  Hi,

  I am trying to write a piece of T-SQL code which will accept a database name as a parameter and return the total size (in pages) of the datafiles (not logfiles) in that database.

  I've tried using the following code but it doesn't seem to like the tablename variable.

  declare @SQLString NVARCHAR(500)

  declare @ParmDefinition NVARCHAR(500)

  declare @dbname        varchar(32)

  declare @dbsize        dec(15,2)

  declare @table VARCHAR(30)

  select @dbname = 'Northwind'

  select @table = @dbname+'.dbo.sysfiles'

  select @SQLString = N'select @databasesize = sum(size) from @tablename where (status & 64 = 0)'

  select @ParmDefinition = N'@tablename VARCHAR(30), @databasesize int OUTPUT'

  execute sp_executesql

  @SQLString,

  @ParmDefinition,

  @tablename = @table,

  @databasesize = @dbsize OUTPUT

  select @dbsize

   

  Can someone please take a look.

   

  Thanks in advance

  Paul

   

• kirkysql

  Old Hand

  Points: 350

  More actions

  February 9, 2004 at 11:18 am

  #493284

  Found a solution in another forum:

  DECLARE @TableName NVARCHAR(100)

  DECLARE @SQLString NVARCHAR(500)

  DECLARE @ParmDefinition NVARCHAR(500)

  DECLARE @dbsize int

  DECLARE @dbname VARCHAR(30)

  SET @dbname = 'Northwind'

  SET @TableName = @dbname + N'.dbo.sysfiles'

  SET @ParmDefinition = N'@dbsize int out'

  SET @SQLString = N'SELECT @dbsize = sum(size) FROM ' + @TableName + N' where (status & 64 = 0)'

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @dbsize out

  print @dbsize

  This query is a basis for a stored procedure which will return database space usage information.

  Thanks

  Paul