Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Using XP_EXECRESULTSET To Obtain Database Size Information and More Expand / Collapse
Author
Message
Posted Tuesday, July 04, 2006 10:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 12, 2010 8:45 AM
Points: 293, Visits: 39
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



Post #292046
Posted Tuesday, August 22, 2006 10:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 18, 2011 12:32 PM
Points: 59, Visits: 278
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.

Post #303247
Posted Tuesday, August 22, 2006 11:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4

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."

Post #303281
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse