SQL Server - Skip querying a database if it is in RESTORING state-CodePudding

I have this script that I found online and have adapted to my database instance. What it does is looks to see if a specific table exists in all the SQL Server databases within the instance. If the table is found and if not empty then it will list the name of the database where the table was found. Its all good so far however, if a database is in Restoring state then my script gets stuck at that database. I tried a different options such as STATE = 0 (which is ONLINE) or state_desc != 'RESTORING' in the 'Where' clause but it still fails. The databases in Restoring state isn't the worry here as sometimes someone may be genuinely trying to restore. All I want to do is not to query that database if its not ONLINE. I wanted to somehow make use of SELECT DATABASEPROPERTYEX ('DatabaseName', 'Status') but can't figure out how. I am not a T-SQL master, so really hoping if someone can tweak this query just to skip the DBs in Restoring state. This will be much appreciated, thank you..

SET NOCOUNT ON;  
IF OBJECT_ID (N'tempdb.dbo.#temptbl') IS NOT NULL DROP TABLE #temptbl  
CREATE TABLE #temptbl ([COUNT] INT , SiteDBName VARCHAR(50) ) 
DECLARE @TableName NVARCHAR(50)  
SELECT @TableName = '[dbo].[PRJMGTLocation]'  
DECLARE @SQL NVARCHAR(MAX)  
SELECT @SQL = STUFF( 
        (     SELECT CHAR(13)   'SELECT '''   name   ''', COUNT(1) FROM ['   name   '].'   @TableName    
        FROM sys.databases     
        WHERE OBJECT_ID(name   '.'   @TableName) IS NOT NULL   
        --  and STATE = 0 and state_desc != 'RESTORING'   
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')  

INSERT INTO #temptbl (SiteDBName, [COUNT])              

EXEC sys.sp_executesql @SQL  

SELECT * FROM #temptbl t where COUNT >=1 ORDER BY SiteDBName asc

CodePudding user response：

Can you try the following slight tweak:

select SQL = Stuff( 
    (select Char(13)   'SELECT '''   name   ''', COUNT(1) FROM '   QuoteName(name)   '.'   @TableName    
    from (select top(1000) name from sys.databases  where state=0   )d
    where Object_Id(d.name   '.'   @TableName) is not null   
    for xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

The issue is that the filtering out of rows from sys.databases is happening after the object_id() lookup, using a row-goal in a sub-query can coerce SQL Server to do it in reverse.