这个是查找全库中存在的某个字段要是看不懂的话,注意看英文注释--create database DBA --GO --use DBA --go --create table find_working (databasename varchar(15),name varchar(70), type varchar(30)) --GO--***************************************************************** -- Proc Name: Find -- -- Purpose: This proc will find any string in all SQL code on this -- server. It will not look in the data itself, but only -- in code -- -- Called By: anyone -- -- Created By: Michael F. Berry -- -- Created on: 12/19/2006 -- -- Modified By: Michael F. Berry -- -- Modified Dte:1/25/2007 -- -- Modified Rsn:Make it put out into one main recordset for clarity --***************************************************************** --exec DBA.dbo.[Find] 'Account'ALTER Procedure [dbo].[Find] ( @search varchar(150), @db varchar(200) = 'all' ) as set nocount on --declare @search varchar(150) --set @search = 'temp' declare @databasename as varchar(200) truncate table DBA.dbo.find_working select 'Searching For: '''+ @search + ''''declare Curse cursor local fast_forward for select distinct name from master.dbo.sysdatabases where name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData') and ( @db = 'all' or name = @db )open Curse fetch next from Curse into @databasename while @@fetch_status = 0 begin exec ('use ' + @databasename + ' insert into DBA.dbo.find_working select distinct ''' + @databasename + ''', cast(o.[name] as varchar(40)) as objectname, o.type -- left(c.text,50) as place from syscomments c inner join sysobjects o ON c.[id] = o.[id] where c.[text] like ''%' +@search+ '%'' order by cast(o.[name] as varchar(40)) ') fetch next from Curse into @databasenameendclose Curse deallocate Curse select databasename,name,type from DBA.dbo.find_working order by databasename,name set nocount off--Put this code on a common database (or master) --and put a proc in each db to call it --so no matter what DB your in you can call it. --It will output the database name, code object name and type. --It is handy when you want to see what code touches a table --that you are considering changing. Very cool code.
打开profiler 看看生成报表的时候执行的是哪个SQL语句。
或者全库扫描你的关键数据,看看是在哪个表里。
--GO
--use DBA
--go
--create table find_working (databasename varchar(15),name varchar(70), type varchar(30))
--GO--*****************************************************************
-- Proc Name: Find
--
-- Purpose: This proc will find any string in all SQL code on this
-- server. It will not look in the data itself, but only
-- in code
--
-- Called By: anyone
--
-- Created By: Michael F. Berry
--
-- Created on: 12/19/2006
--
-- Modified By: Michael F. Berry
--
-- Modified Dte:1/25/2007
--
-- Modified Rsn:Make it put out into one main recordset for clarity
--*****************************************************************
--exec DBA.dbo.[Find] 'Account'ALTER Procedure [dbo].[Find]
(
@search varchar(150),
@db varchar(200) = 'all'
)
as
set nocount on
--declare @search varchar(150)
--set @search = 'temp'
declare @databasename as varchar(200)
truncate table DBA.dbo.find_working
select 'Searching For: '''+ @search + ''''declare Curse cursor local fast_forward
for
select distinct name from master.dbo.sysdatabases
where name not in
('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData')
and
(
@db = 'all'
or name = @db
)open Curse
fetch next from Curse into @databasename
while @@fetch_status = 0
begin
exec ('use ' + @databasename + ' insert into DBA.dbo.find_working
select distinct
''' + @databasename + ''',
cast(o.[name] as varchar(40)) as objectname,
o.type -- left(c.text,50) as place
from
syscomments c
inner join
sysobjects o ON
c.[id] = o.[id]
where
c.[text] like ''%' +@search+ '%''
order by cast(o.[name] as varchar(40))
')
fetch next from Curse into @databasenameendclose Curse
deallocate Curse
select databasename,name,type
from DBA.dbo.find_working
order by databasename,name
set nocount off--Put this code on a common database (or master)
--and put a proc in each db to call it
--so no matter what DB your in you can call it.
--It will output the database name, code object name and type.
--It is handy when you want to see what code touches a table
--that you are considering changing. Very cool code.