Pls help me to complete the procedure

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 2000 Forums

Transact-SQL (2000)

Pls help me to complete the procedure

Author

Topic

krishnarajeesh
Yak Posting Veteran

67 Posts

Posted - 2007-03-19 : 01:48:12

Hi team,

I want to call all databases inside the Server,and want to do some queries on these dbs.

For this I tried a while loop , but unfortunately I cant use

"USE DATABASENAME" command inside a stored procedure.

Pls help me how to loop thru all dbs in a stored proc.

Thanks In Advance...Krishna

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-19 : 11:41:25

You can use undocumented sp_msforeachdb command to run query for each of the databases on the server.

sp_msforeachdb 'print ''?'''

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

krishnarajeesh
Yak Posting Veteran

67 Posts

Posted - 2007-03-20 : 03:28:39

Hi Harsh,
Thanx for ur help :)
But I got a better way with cursor + the below code (since this one suits my purpose better)
set @qry= (select 'use '+ @dbname+' dbcc shrinkfile('+@logfilename+',TruncateOnly)')
exec (@qry)

Also using the same will not through any error for the use of "Use database" cmd in procedure

rgds...Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-20 : 04:07:29

How do you get all database names and all logfile names?

Peter Larsson
Helsingborg, Sweden

krishnarajeesh
Yak Posting Veteran

67 Posts

Posted - 2007-03-20 : 22:53:35

Hi Larsson,

==> Use the below code to get all the databases in the server.

select name from master..sysdatabases where name not in ('tempdb','model','master','pubs','msdb','Northwind','distribution')

==> Now get all the database names to @dbname thru cursor, & use the below code
for get the log files. Suppose we need only .ldf files, use the below code for the same.

set @sqltext = 'select * from ' +@dbname+ '.dbo.sysfiles where filename like ''%ldf%'''
insert into #temp exec sp_sqlexec @sqltext

==> Now all these .ldf files are inserted into a temp table.

Regards...Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources