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 |
|
nandac
Starting Member
27 Posts |
 Posted - 2008-12-02 : 10:56:40
|
| i want to go into each database and then print the database name. the following is not working. appreciate some help.declare @cmd nvarchar(1024)DECLARE AllDatabases CURSOR FORSELECT name FROM master..sysdatabases OPEN AllDatabasesDECLARE @DB NVARCHAR(128)FETCH NEXT FROM AllDatabases INTO @DBWHILE (@@FETCH_STATUS = 0)BEGIN set @cmd='use ' + @DB execute(@cmd) select db_name() FETCH NEXT FROM AllDatabases INTO @DBENDCLOSE AllDatabasesDEALLOCATE AllDatabases |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-02 : 11:00:04
|
| Why cursors?? Just run sp_helpdb or sp_databases. |
 |
|
|
nandac
Starting Member
27 Posts |
Posted - 2008-12-02 : 11:06:39
|
| well i need to run a space statistics script within each database. so i need a loop kind of thing. |
|
|
|
nandac
Starting Member
27 Posts |
Posted - 2008-12-02 : 11:08:09
|
| or is there an option in enterprise manager or management studio to execute a script in all databases? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-02 : 11:31:14
|
| Simply run this to get what you need.EXEC sp_MSForeachdb 'USE ? BEGINEXEC sp_spaceusedEND' |
|
|
|
nandac
Starting Member
27 Posts |
Posted - 2008-12-02 : 13:22:48
|
| hey that was great. is there anyway i can put it to run as a job on enterprise manager or something to run daily morning at a particular time? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-02 : 13:30:39
|
| Yes. Put it in SQLServer agent-Jobs and Schedule it to run. |
|
|
|
|
|
|
printing db name