June 14, 2004 at 2:24 pm
Is there a way to execute the FILEPROPERTY function against multiple databases with-in a stored procedure.
I want to execute [SELECT FILEPROPERTY('MyDB_Log', 'SPACEUSED')]
against all database log files on my MS SQL server.
I tried executing the USE <database> SQL statement to change the focus of the database with-in my stored procedure, but apparently this statement cannot be complied into a stored procedure.
Does any one know of a way to change database focus within a stored procedure?
Thanks
Joel
June 14, 2004 at 5:04 pm
Try
sp_msforeachdb '
print ''?''
SELECT FILEPROPERTY(''?_Log'', ''SPACEUSED'')'
as long as all are dbname_log will work otherwise will get nulls for those not.
June 15, 2004 at 1:45 am
You could create a sp_ stored procedure in the master database. sp_ stored procedures in master can be called from any database, and are the only type of stored procedures that can use tables/properties local to the database you are in. I found this method the easiest if I need some info for ALL databases. I use the prefix sp__, so I know this is a sp_ procedure not created by microsoft ... Here is an example :
use master
go
-- drop the procedure if it allready exists
if object_id ('dbo.sp__logspaceused') is not null drop proc dbo.sp__logspaceused
go
create proc dbo.sp__logspaceused as
-- This procedure returns a query result with
-- Db = database name
-- name : logical name of the log file(s)
-- SpaceUsed : space used for the logfile
-- The procedure will insert this info into the table #sp__logspaceused if this table exists ...
begin
set nocount on
if object_id('tempdb..#sp__logspaceused') is null -- select if table does not exists
select Db=db_name(),name,SpaceUsed=FILEPROPERTY(name,'SpaceUsed')
from dbo.sysfiles1
where status & 0x40 = 0x40
else -- insert if exists
insert #sp__logspaceused
select Db=db_name(),name,SpaceUsed=FILEPROPERTY(name,'SpaceUsed')
from dbo.sysfiles1
where status & 0x40 = 0x40
end
go
-- if you want to get the results for all the databases on your system, try this :
if object_id ( 'tempdb..#sp__logspaceused' ) is not null drop table #sp__logspaceused
go
-- Create temp table to hold the data
select Db=db_name(),name,SpaceUsed=FILEPROPERTY(name,'SpaceUsed')
into #sp__logspaceused
from dbo.sysfiles1
where status & 0x40 = 0x40
and 1=2
declare @proc2exe sysname
-- cursor to loop through all database names
declare Alldb cursor for select proc2exe = name+'.dbo.sp__logspaceused' from master.dbo.sysdatabases for read only
open Alldb
goto NextDB
while @@fetch_status = 0
begin
exec @proc2exe
NextDB: fetch Alldb into @proc2exe
end
close Alldb
deallocate Alldb
-- show the results
select * from #sp__logspaceused
go
drop table #sp__logspaceused
go
June 15, 2004 at 4:47 am
This is also usefull.
dbcc sqlperf ( LOGSPACE)
June 15, 2004 at 8:13 am
This code will cycle through all databases on a specific server and return all Databses, FileGroups and FileNames with the space used. It's just an exercise to show you how to get File Groups and File Names as well as dynamically switch databases and execute code on those databases.
If you output the @s-2 variable the code will be already formatted. That's what the CHAR(13) is for, it's a new line (line feed) character.
Good Luck
CREATE TABLE #TempData
(DBName sysname
,FGName sysname
,FNName sysname
,SpaceUsed int
)
DECLARE @s-2 nvarchar(2000)
DECLARE @Cursor cursor
,@Cursor_Status int
,@Cursor_DBName sysname
SET @Cursor = CURSOR FORWARD_ONLY FOR
SELECT [name]
FROM master..sysdatabases
ORDER BY [name]
OPEN @Cursor
FETCH @Cursor
INTO @Cursor_DBName
SET @Cursor_Status = @@FETCH_STATUS
WHILE @Cursor_Status = 0
BEGIN
SET @s-2 = 'USE ' + @Cursor_DBName + CHAR(13)
SET @s-2 = @s-2 + 'DECLARE @SpaceUsed int' + CHAR(13)
SET @s-2 = @s-2 + 'DECLARE @Cursor1 cursor' + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_Status int' + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_FGName sysname' + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_FNName sysname' + CHAR(13)
SET @s-2 = @s-2 + 'SET @Cursor1 = CURSOR FORWARD_ONLY FOR' + CHAR(13)
SET @s-2 = @s-2 + 'SELECT DISTINCT b.[groupname], a.[name]' + CHAR(13)
SET @s-2 = @s-2 + 'FROM sysfiles a' + CHAR(13)
SET @s-2 = @s-2 + 'JOIN sysfilegroups b' + CHAR(13)
SET @s-2 = @s-2 + ' ON a.groupid = b.groupid' + CHAR(13)
SET @s-2 = @s-2 + 'open @Cursor1' + CHAR(13)
SET @s-2 = @s-2 + 'FETCH @Cursor1' + CHAR(13)
SET @s-2 = @s-2 + ' INTO @Cursor_FGName' + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_FNName' + CHAR(13)
SET @s-2 = @s-2 + 'SET @Cursor_Status = @@FETCH_STATUS' + CHAR(13)
SET @s-2 = @s-2 + 'WHILE @Cursor_Status = 0' + CHAR(13)
SET @s-2 = @s-2 + ' BEGIN' + CHAR(13)
SET @s-2 = @s-2 + ' INSERT INTO #TempData' + CHAR(13)
SET @s-2 = @s-2 + ' (DBName' + CHAR(13)
SET @s-2 = @s-2 + ' ,FGName' + CHAR(13)
SET @s-2 = @s-2 + ' ,FNName' + CHAR(13)
SET @s-2 = @s-2 + ' ,SpaceUsed)' + CHAR(13)
SET @s-2 = @s-2 + ' VALUES' + CHAR(13)
SET @s-2 = @s-2 + ' (' + QUOTENAME(@Cursor_DBName, '''') + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_FGName' + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_FNName' + CHAR(13)
SET @s-2 = @s-2 + ' ,FILEPROPERTY(@Cursor_FNName, ' + QUOTENAME('SPACEUSED', '''') + ')' + CHAR(13)
SET @s-2 = @s-2 + ' )' + CHAR(13)
SET @s-2 = @s-2 + ' FETCH @Cursor1' + CHAR(13)
SET @s-2 = @s-2 + ' INTO @Cursor_FGName' + CHAR(13)
SET @s-2 = @s-2 + ' ,@Cursor_FNName' + CHAR(13)
SET @s-2 = @s-2 + ' SET @Cursor_Status = @@FETCH_STATUS' + CHAR(13)
SET @s-2 = @s-2 + ' END' + CHAR(13)
SET @s-2 = @s-2 + ' CLOSE @Cursor1' + CHAR(13)
EXEC sp_ExecuteSQL @s-2
FETCH @Cursor
INTO @Cursor_DBName
SET @Cursor_Status = @@FETCH_STATUS
END
CLOSE @Cursor
SELECT * FROM #TempData
ORDER BY DBName, FGName, FNName
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply