Tuesday, April 14, 2015

CMS: Find All Databases in all Servers that don't have a Specified Table

This code is for CMS (Central Management Servers), and displays all the databases on all the servers that don't have a specified table.

-- For all user databases in all servers, which ones don't have a "Customer" table? 
set nocount on

declare @Schema sysname     set @Schema = 'dbo'
declare @Table  sysname     set @Table  = 'Customer'
declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

    if db_id() > 4
        if object_id(N''' + @Schema + '.' + @Table + ''', N''U'') is null
            print @@SERVERNAME + '': '' + ''?'' + '' - ' + @Schema + '.' + @Table + ' does not exist'''

exec sp_msforeachdb @Sql

0 comments:

Post a Comment