I have a script that creates a temp table of all views that contain certain information. Well this worked great, UNTIL last week when several databases were disconnected but the view associated with that database was left. So now I am left with a procedure that produces error after error bc the database no longer exists. Is there a way to tweak my current syntax to ignore or skip if the source database does not exist so that I still get the desired outcome?
CREATE TABLE #Views (ID INT IDENTITY NOT NULL, TableNames VARCHAR(1000))
CREATE TABLE #FinalResults (TableNames VARCHAR(1000))
INSERT INTO #Views (TableNames)
SELECT '['+C.TABLE_CATALOG+'].['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']' TableName
FROM INFORMATION_SCHEMA.COLUMNS C
JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG AND
C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'View'
AND COLUMN_NAME = 'State'
GROUP BY '['+C.TABLE_CATALOG+'].['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']'
DECLARE @N INT, @Str NVARCHAR(2000), @TableName NVARCHAR(2000), @MAXID INT
SET @N = 1
SELECT @MAXID = MAX(ID)
SELECT @TableName = TableNames
WHERE ID = @N
SET @Str =
' INSERT INTO #FinalResults(TableNames)
SELECT TOP 1 '''+@TableName+'''
WHERE EXISTS(SELECT 1 FROM '+@TableName+' WHERE [State] LIKE ''%AL%'') '
EXEC SP_EXECUTESQL @Str
SET @N = @N + 1
DROP TABLE #Views, #FinalResults
Are you saying objects in dropped databases are still showing up in the system catalogs?
I believe so, the actual database has been dropped, but the view which exists in a different database is still 'existing'.
For example all the data exists on FireFox1, but the actual Master_FF table has been dropped, however I am trying to query the view that exists on LL so I get an error. Does that make more sense as to what my issue is?