none
Declare a variable with stored procedure

    Question

  • I am new to T-SQL and I need help with an operation. It's all about getting the size of datafiles and logfiles for each databases.

    select 'SELECT ''' + name + ''' AS DBNAME,' +
    'sum(size * 8 /1024) AS MB from ' + name + '.dbo.sysfiles union '
    from sysdatabases
    order by name

    (This query gives me queries for each databases in the instance)

    Result :

    SELECT 'master' AS DBNAME,sum(size * 8 /1024) AS MB from master.dbo.sysfiles union
    SELECT 'model' AS DBNAME,sum(size * 8 /1024) AS MB from model.dbo.sysfiles union
    SELECT 'msdb' AS DBNAME,sum(size * 8 /1024) AS MB from msdb.dbo.sysfiles union
    SELECT 'Northwind' AS DBNAME,sum(size * 8 /1024) AS MB from Northwind.dbo.sysfiles union
    SELECT 'pubs' AS DBNAME,sum(size * 8 /1024) AS MB from pubs.dbo.sysfiles union
    SELECT 'tempdb' AS DBNAME,sum(size * 8 /1024) AS MB from tempdb.dbo.sysfiles

    Then I have to launch another query with these to get my results.

    What I'm trying to do it skip that middle part and have it execute the queries by itself.

    The query below works well but I don't like how the results are displayed.

    EXEC sp_MSforeachdb 'USE ? SELECT name,filename AS Path,(size*8)/1024 AS MB

    FROM dbo.sysfiles'

     

    Friday, August 06, 2010 6:59 PM

Answers

  • Try

    CREATE Table #vTable (name varchar(100),Path varchar(255), MB int)
    Insert into #vTable
      EXEC sp_MSforeachdb 'USE [?] SELECT name,filename AS Path,(size*8)/1024 AS MB
      FROM dbo.sysfiles'
    
    Select * from #vTable
    Drop table #vTable
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Kalman TothModerator Friday, August 06, 2010 10:53 PM
    • Marked as answer by KJian_ Friday, August 13, 2010 7:40 AM
    Friday, August 06, 2010 8:33 PM
    Moderator

All replies

  • Please see 

     

    How to get information about all databases without a loop

    for samples of how to get this.

    If you construct a valid @SQL string, then

    execute (@SQL) -- will do the actual query

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 06, 2010 7:02 PM
    Moderator
  • You can get the vals from sp_MSForEachDB to table variable and display result as you want;

    Declare @vTable Table (name varchar(100),Path varchar(max), MB int)
    Insert into @vTable
    	EXEC sp_MSforeachdb 'USE ? SELECT name,filename AS Path,(size*8)/1024 AS MB
    	FROM dbo.sysfiles'
    
    Select * from @vTable
    
    Friday, August 06, 2010 7:12 PM
  • You can get the vals from sp_MSForEachDB to table variable and display result as you want;

    Declare
     @vTable Table
     (name
     varchar
    (100),Path varchar
    (max
    ), MB int
    )
    Insert
     into
     @vTable
    	EXEC
     sp_MSforeachdb 'USE ? SELECT name,filename AS Path,(size*8)/1024 AS MB
    	FROM dbo.sysfiles'
    
    
    Select
     * from
     @vTable
    

    Impossible to use execute as source for insertion in a table variable.
    Friday, August 06, 2010 7:16 PM
  • Why?

    It works good.

    Friday, August 06, 2010 7:21 PM
  • In SQL 2000 you can not use INSERT EXECUTE with table variables.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 06, 2010 7:24 PM
    Moderator
  • Here is my script in SQL server 2005 and I have the same problem.

    Declare @vTable Table (name varchar(100),physical_name varchar(100), size int)
    Insert into @vTable
        EXEC sp_MSforeachdb 'USE ? SELECT name,physical_name,(size*8)/1024 AS MB
        FROM sys.database_files'

    Select * from @vTable

    Friday, August 06, 2010 7:25 PM
  • As I said, change declare @vTable into create table #vTable. It's possible your database is in compatibility mode 2000.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    I am not sure what to do with that. I do not have enough experience to make the corrections.

    Thank you for your help.

    Friday, August 06, 2010 7:31 PM
  • In SQL 2000 you can not use INSERT EXECUTE with table variables.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    That's right, this is a good example why it's important to give all informations, includ the SQL Server Version.
    Friday, August 06, 2010 7:34 PM
  • Try like this;

    CREATE Table #vTable (name varchar(100),Path varchar(max), MB int)
    Insert into #vTable
    	EXEC sp_MSforeachdb 'USE ? SELECT name,filename AS Path,(size*8)/1024 AS MB
    	FROM dbo.sysfiles'
    
    Select * from #vTable
    Drop table #vTable
    
    
    Friday, August 06, 2010 7:35 PM
  • perfect solution. Thank you all, something more in my bank of knowledge.
    Friday, August 06, 2010 7:36 PM
  • One extra solution based on your original script

    declare @Sql varchar(max)
    select @SQL =coalesce(@SQL + char(13) + 'UNION ALL 
    ' ,'') + 'SELECT ''' + name + ''' AS DBNAME,' + 
    'sum(size * 8 /1024.0) AS MB from ' + name + '.dbo.sysfiles' 
    from sys.databases
    order by name
    
    execute (@SQL)
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 06, 2010 7:44 PM
    Moderator
  • But if you are using SQL Server 2005, the last solution posted should have worked. Did you check your compatibility level?

     (Righ click your database in management studio --> Goto options and check there. Compatibility Level should be SQL Server 2005 (90))

    Friday, August 06, 2010 7:44 PM
  • What would be the
    from
     sys.databases

    for sql server 2000 ?
    Friday, August 06, 2010 7:50 PM
  • It should be sysdatabases in SQL Server 2000
    Friday, August 06, 2010 7:51 PM
  • CREATE Table #vTable (name varchar(100),Path varchar(255), MB int)
    Insert into #vTable
        EXEC sp_MSforeachdb 'USE ? SELECT name,filename AS Path,(size*8)/1024 AS MB
        FROM dbo.sysfiles'

    Select * from #vTable
    Drop table #vTable

    This is perfect on 2000 to 2008.

    The only problem left I have is if a database has a name like my_database-name. I receive the error Database 'my_database' does not exist. Make sure that the name is entered correctly.

    Any tips ?

    BTW Thanks all !

    Friday, August 06, 2010 8:13 PM
  • Try

    CREATE Table #vTable (name varchar(100),Path varchar(255), MB int)
    Insert into #vTable
      EXEC sp_MSforeachdb 'USE [?] SELECT name,filename AS Path,(size*8)/1024 AS MB
      FROM dbo.sysfiles'
    
    Select * from #vTable
    Drop table #vTable
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Kalman TothModerator Friday, August 06, 2010 10:53 PM
    • Marked as answer by KJian_ Friday, August 13, 2010 7:40 AM
    Friday, August 06, 2010 8:33 PM
    Moderator
  • Perfect ! Perfect !
    Friday, August 06, 2010 8:49 PM