Agent Jobs

Quick Object Search Across All Databases

Want to search the sysobjects tables across all databases? This little bit of logic should help. Sure; it’s not hard to write a query across all databases… pffft… kid stuff, but it’s nice to have something to get you going if you need it. Save you a few keystrokes. Ok; so what this does is builds a list of queries in a #logic_table, and then runs the results into a #results table.

In this example I’m looking for a few older objects floating around so I can concatenate a query to revoke permissions from them. As you can see below; they are the RTblDMProps, mswebtask, and dtproperties objects.

As you can see; I’ve already confirmed the objects exist so I can write some logic to revoke the permissions. I’ve got maybe a couple hundred databases on this box so it’s nice to get a tidy little list with all the code in the temp tables so I can tweak, or expand on it if needed.

use master;
set nocount on


-- basic search across all databases be sure to repalce MyObject with the object you're searching for.
select
'use [' + upper([name]) + ']; select upper(db_name()), [name], [xtype] from [' + name + ']..sysobjects where [name] in (''MyObject'');'
from sys.databases
where [name] not in ('tempdb')

-- the remaining logic will produce a script on removing rights from the desired object(s).
if object_id('tempdb..#logic_table') is not null drop table #logic_table
create table #logic_table	([logic] varchar(max))
insert into #logic_table	([logic])
select 'use [' + upper([name]) + ']; select upper(db_name()), [name], [xtype] from [' + name + ']..sysobjects where [name] in (''MyObject1'', ''MyObject2'', ''MyObject3'');'
from  master.sys.databases where database_id <> 2

if object_id('tempdb..#results') is not null drop table #results
create table	#results ([lineid] int identity(1,1), [database] varchar(255), [name] varchar(255), [xtype] varchar(2))
declare @sql	varchar(max) 
set	@sql	= ''
select	@sql	= @sql + [logic] from #logic_table
insert into #results ([database], [name], [xtype])
exec (@sql) 
select * from #results where [database] is not null

-- build revoke statement
select 'use [' + [database] + ']; revoke select, insert, update, delete, references on ' + [name] + ' to public;'
from #results

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s