Using XP_EXECRESULTSET To Obtain Database Size Information and More

  • Take out the sid>1 conditional from the where clause.

    One of the readers, Scott, pointed out that the SID=1 means sa created the database and that this isn't a good way to limit tha query to non-system databases. That should solve the problem. You are, as you pointed out, probably excluding all of your user databases as well because they were created by 'sa'.

    Declare @DBName sysname

    Declare @SqlCmd Nvarchar(2000)

    Set @DBName = ''

    While @DBName Is not NULL

    Begin

     Select @DBName = Min(Name) From master..sysdatabases Where Name > @DBName

     Set @SqlCmd = 'SELECT ''SELECT name AS [File], filename as [File Name]

        , CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB]

        , CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used]

       , CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space] FROM SYSFILES'''

     IF @DBName is NULL Break

     Exec Master..xp_execresultset @SqlCmd,@DBName

    End

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."

  • Good ideas. Just remember that this is still unsupported so don't call Redmond if you have problems with it.

     

    That's what we're all here for anyway.

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."

  • Really neat. Thanks.

    I appreciated the cut and paste script in the comments. The graphic image of the script in the article was frustrating.

    -Robert

     

  • Yeah, I noticed that. Sorry about the original.

    Glad you found this useful.

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."

  • Got it ...

    Thanks

  • Warning : when using this procedure the result set it executes is run on the one of the main processor threads of SQL Server, not your connection thread. If you perform a long running query it can bring SQL Server to a stand still. Do not use on production servers - or at least with care.

    It happened to me (luckily on a DEV server) and my colleagues were asking me why SQL Server had stopped responding to their queries. Because the main thread was handling mine!

    You have been warned 🙂

  • Great article and very helpful.

    however, I wanted to experiment with it... why is the SQL a image and not text on the page? Can't copy and paste imaged SQL.

  • I'm afraid that's because the way I sent the article to the editors.

    Here's the code in text:

    DECLARE @DBName SYSNAME

    DECLARE @SQLCmd NVARCHAR(2000)

     

    SET @DBNAME=''

     

    WHILE @DBNAME IS NOT NULL

          BEGIN

    SELECT @DBNAME=MIN(name) FROM MASTER..SYSDATABASES WHERE sid > 1 AND name>@DBNAME

                SET @SQLCmd='SELECT ''SELECT name AS [File],

                      filename as [File Name],

                      CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB],

                      CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used],

                      CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space]

                      FROM SYSFILES'''

                --PRINT @SQLCmd

                IF @DBName IS NULL BREAK

                EXEC MASTER..XP_EXECRESULTSET @SQLCmd, @DBName

          END

    and

    DECLARE @SQL NVARCHAR(4000)

    DECLARE @USER SYSNAME

    DECLARE @DATABASE SYSNAME

    DECLARE @PERMISSION SYSNAME

     

    SET @USER='BOB' –- Replace with name of user

    SET @DATABASE='MyDB' –- Replace with database name

    SET @PERMISSION='EXECUTE' -- Replace with rights. You can use a comma separated list here. For example 'SELECT,UPDATE' if you want to modify permissions on tables

     

    SET @SQL='SELECT ''GRANT '+@PERMISSION+' ON ''+NAME+'' TO '+@USER+' ''FROM '+@DATABASE+'..SYSOBJECTS WHERE TYPE=''P''  AND NAME LIKE ''USP%'''

     

    EXEC SP_EXECUTESQL @SQL

     

    EXEC MASTER..XP_EXECRESULTSET @SQL, @DATABASE

     

    -- J.T.

    "I may not always know what I'm talking about, and you may not either."

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply