database offline status and to find the table\database object location.

Question

database offline status and to find the table\database object location.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 23, 2010 at 11:11 pm

#219607

1. Can you please suggest me a suitable query to find out whether any database is in "offline" status in the SQL Server?
2. I need a generic query to find out any database object(tables,SP,Triggers) either located in any of the databases in the SQL Server rather than searching only in a single database.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

Sourav-657741 (4/23/2010)
1. Can you please suggest me a suitable query to find out whether any database is in "offline" status in the SQL Server?

the following will work for sql2005 on

select name, state from sys.databases

where state = 6

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

vidya_pande SSCertifiable Points: 5275 More actions · Answer 2

for second question--

Search object across all databases in instance--

http://www.sqlservercentral.com/Contributions/Edit/31650

george sibbald SSC Guru Points: 104210 More actions · Answer 3

select name from sys.databases where state_desc = 'offline'

or you can use databasepropertyex or even databaseproperty (pre 2005)

---------------------------------------------------------------------

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

for second question--

Search object across all databases in instance--

http://www.sqlservercentral.com/Contributions/Edit/31650

----------------------------------------------------------

The above link is not opening!!

Thanks.

rVadim Hall of Fame Points: 3969 More actions · Answer 5

There were plenty of postings about object search:

http://www.sqlservercentral.com/search/?q=search+objects+for+text&t=s

I also use SQL Search - still free from RedGate:

http://www.red-gate.com/products/SQL_Search/index.htm

--Vadim.

--Vadim R.