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 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply