Loop to change which database to use

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Loop to change which database to use

Author

Topic

j2dna
Starting Member

11 Posts

Posted - 2010-04-26 : 11:05:17

Not sure if this is possible. I'm wanting to get stats on each database that is configured. I get a syntax error at the line use @db.

declare @db as varchar(100);
declare @dbid as int;
declare @dbid_max as int;
set @dbid = 1
set @dbid_max = (select max(dbid) from master.dbo.sysdatabases)

While @dbid <= @dbid_max
BEGIN

set @db = (select name from master.dbo.sysdatabases where dbid = @dbid)

use @db

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

set @dbid = @dbid + 1

END

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 11:24:38

You cant use USE with a variable like this. You can do it with dynamic SQL though.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-26 : 11:35:49

try this


DECLARE @sql NVARCHAR(MAX)

SELECT @sql = N''

SELECT @Sql = @sql + '
SELECT
	' + QUOTENAME([name], '''') + ' COLLATE DATABASE_DEFAULT AS [Database] 
	, [name] COLLATE DATABASE_DEFAULT AS [FileName] 
	, [size] /128.0 AS CurrentSizeMB
	, [size] / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) / 128.0 AS FreeSpaceMB
FROM
	' + QUOTENAME([name]) + '.sys.database_files
UNION'
FROM
	sys.databases

SET @sql = LEFT(@sql, LEN(@sql) - 7)

SELECT @sql = @sql + '
ORDER BY
	[database]'

--PRINT @sql

EXEC sp_executeSql @sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

j2dna
Starting Member

11 Posts

Posted - 2010-04-26 : 12:08:16

quote:
Originally posted by Transact Charlie

try this

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = N''

SELECT @Sql = @sql + '
SELECT
' + QUOTENAME([name], '''') + ' COLLATE DATABASE_DEFAULT AS [Database]
, [name] COLLATE DATABASE_DEFAULT AS [FileName]
, [size] /128.0 AS CurrentSizeMB
, [size] / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) / 128.0 AS FreeSpaceMB
FROM
' + QUOTENAME([name]) + '.sys.database_files
UNION'
FROM
sys.databases

SET @sql = LEFT(@sql, LEN(@sql) - 7)

SELECT @sql = @sql + '
ORDER BY
[database]'

--PRINT @sql

EXEC sp_executeSql @sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

That works....but I only get space used for the DB I have selected.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-26 : 12:22:40

Hmmm.

Ok -- go old skool then


IF OBJECT_ID('tempDb..#results') IS NOT NULL DROP TABLE #results

CREATE TABLE #results (
	[Database] VARCHAR(512)
	, [Filename] VARCHAR(512)
	, [CurrentSizeMB] DECIMAL(13,4)
	, [FreeSpaceMB] DECIMAL(13,4)
	)

DECLARE @sql NVARCHAR(MAX)
DECLARE @dbName VARCHAR(255)

DECLARE dbCursor CURSOR LOCAL READ_ONLY FOR SELECT
	[name]
FROM
	sys.databases

OPEN dbCursor

	FETCH NEXT FROM dbCursor INTO @dbNAme

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @sql = N'
		USE ' + QUOTENAME(@dbName) + '

		INSERT INTO #results (
			[Database]
			, [Filename]
			, [CurrentSizeMB]
			, [FreeSpaceMB]
			)
		SELECT
			@dbNAme 
			, [name]
			, [size] /128.0 AS CurrentSizeMB
			, [size] / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) / 128.0 AS FreeSpaceMB
		FROM
			sys.database_files'

		EXEC sp_ExecuteSql
			@sql
			, N'@dbName VARCHAR(255)'
			, @dbNAme

		FETCH NEXT FROM dbCursor INTO @dbName

	END

CLOSE dbCursor
DEALLOCATE dbCursor

SELECT * FROM #results

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

j2dna
Starting Member

11 Posts

Posted - 2010-04-26 : 12:41:55

Excellent!!!

Thx...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources