List all databases that contain a specific table

Navigation
1

I want to list all the databases of a given server Sql Server (2008 or higher) that contain a certain table. I have seen several examples from cursors, stored procedures that run through the server databases to the use of sp_msforeachdb, which in several places discourages. What would be the best way to do this? I put what I'm doing now: 

    Dim daBBDD As New SqlClient.SqlDataAdapter("SELECT QUOTENAME(name) Name FROM sys.databases WHERE database_id > 4 AND state = 0 ORDER BY name", CnxGestion)
    Dim dtBBDD As New DataTable("DDBB")
    daBBDD.Fill(dtBBDD)

    Dim daTable As SqlClient.SqlDataAdapter
    Dim dtTable As DataTable

    For Each _bbdd As DataRow In dtBBDD.Rows
        daTable = New SqlClient.SqlDataAdapter(String.Format("SELECT * FROM {0}.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TABLA_BUSCADA'", _bbdd(0)), CnxGestion)
        dtTable = New DataTable(_bbdd(0).ToString)
        daTable.Fill(dtTable)
        If dtTable.Rows.Count > 0 Then
            ''Contiene la tabla
        else
            ''No contiene la tabla
        End If
    Next
    
asked by Jaime Capilla 22.02.2017 в 13:27
source

2 answers

2

Try with: 

SELECT 
    name 
FROM 
    sys.databases 
WHERE CASE WHEN 
    state_desc = 'ONLINE' THEN OBJECT_ID(QUOTENAME(name) 
    + '.[dbo].[TABLA_BUSCADA]', 'U') END IS NOT NULL

Taken from this SOeng question

a>

    
answered by 22.02.2017 / 13:35
source
0

A user can not perform queries on BD that does not have permissions. What you can do is execute that code: 

EXECUTE AS USER = 'Domain\User'
query
REVERT

The idea is that the user that you put is one that has select permissions in all the databases (or the necessary ones) in this way even if it is a user without permission who runs the code fragment, the tables will be shown.

    
answered by 24.02.2017 в 23:08
package python project within an .exe show a message of Loading ... ** Angular **