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.
| Author |
Topic |
|
Kim Yoon-seop
Starting Member
2 Posts |
 Posted - 2011-07-12 : 00:37:33
|
| I want to make a procedure to check all databases that I manage.So I try to change current DB using USE statement.A database name is a variable.For example DECLARE @DB_NAME varchar(10)SET @DB_NAME = "BS0002"USE @DB_NAMEWhen I Excuted the query above, I got an error masage.So I altered the query like this.DECLARE @DB_NAME NVARCHAR(10)DECLARE @STMT NVARCHAR(50)SET @DB_NAME = 'BS0002'SET @STMT = 'USE ' + @DB_NAMEEXECUTE sp_executesql @STMTI got a massage "Command(s) completed successfully." but didn't change current database.If anyone who knows the answer, please help me.Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kim Yoon-seop
Starting Member
2 Posts |
Posted - 2011-07-12 : 00:56:02
|
| Thank you tkizer.DECLARE dbname_cursor CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE ('BS%')OPEN dbname_cursorFETCH NEXT FROM dbname_cursorINTO @DB_NAMEWHILE @@FETCH_STATUS = 0BEGINUSE @DB_NAME select @data_size = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) , @log_size = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) , @db_size = @data_size + @log_size from dbo.sysfiles I got all of user database name from SYS.DATABASES.I want to check the size of all user database using cursor so I have to use while statment changing current DB. Can you understand what I want to do?I think you mean that 3 part naming is "BS0001.dbo.sysfiles" .Right? |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-12 : 02:06:50
|
google forsql server sp_msforeachdb file size and you will get a lot of ideas 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Can I use a variable at 'USE' statement?