April 24, 2007 at 9:03 am
How do I search for any given value in a whole database?
April 24, 2007 at 1:16 pm
Would you elaborate a bit more? Are you searching for a column value in all the tables of a database?
Greg
Greg
April 24, 2007 at 1:22 pm
Yes that is correct I wish to search out a given value in all the columns in a database.
April 24, 2007 at 2:20 pm
You'd have to query the tables. Does the column have the same name in all the tables?
Greg
Greg
April 24, 2007 at 2:24 pm
No there is diffrent columns in all the tables, I'm just looking a specific value in one of the tables im not sure which table the column is in though.
April 24, 2007 at 3:06 pm
Doh! You're trying to find which table contains a column with a specific name! Use this query to list tables that have a column. Just replace 'columnname' with the column you're searching for :
select t.name from dbo.syscolumns c join dbo.sysobjects t on c.id = t.id
where c.name = 'columnname'
and t.xtype = 'U'
Greg
Greg
April 25, 2007 at 7:51 am
Andrew,
This may be overkill - but this proc will find a field occurence in any sql module.
I do not take credit for this proc - I got it via SQLCentral newsletter info.
IF (object_id('sp_FindReferences') IS NOT NULL)
BEGIN
PRINT 'Dropping: sp_FindReferences'
DROP procedure sp_FindReferences
END
PRINT 'Creating: sp_FindReferences'
GO
CREATE PROCEDURE sp_FindReferences
(
@string varchar(1000) ='Parcel_User_ID',
@ShowReferences char(1) = 'Y'
)
AS
/*
DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT
exec sp_FindReferences 'fieldname', 'Y'
*/
set nocount on
declare @errnum int ,
@errors char(1) ,
@rowcnt int ,
@output varchar(255)
select @errnum = 0 ,
@errors = 'N' ,
@rowcnt = 0 ,
@output = ''
/****************************************************************************/
/* INPUT DATA VALIDATION */
/****************************************************************************/
/****************************************************************************/
/* M A I N P R O C E S S I N G */
/****************************************************************************/
-- Create temp table to hold results
create table #Results
(
Name varchar(55),
Type varchar(12),
DateCreated datetime,
ProcLine varchar(4000)
)
IF (@ShowReferences = 'N')
BEGIN
insert into #Results
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
ELSE
BEGIN
insert into #Results
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = text
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
IF (@ShowReferences = 'N')
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated
from #Results
order by 2,1
END
ELSE
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated,
ProcLine
from #Results
order by 2,1
END
drop table #Results
GO
IF (object_id('sp_FindReferences') IS NOT NULL)
PRINT 'Procedure created.'
ELSE
PRINT 'Procedure NOT created.'
GO
April 25, 2007 at 9:48 am
I read your question a little differently. Hope I'm not coming out of left field here. Just this week, I needed to find a value in a database - not a column name but actual data. This was in a brand new MOSS database so it's nothing huge (I was just looking/researching - no touching!). And yes.... it's a nested cursor (eee-gad!). Probably not viable in a larger database.
set
nocount on
declare @table varchar(100)
declare @column varchar(100)
declare @criteria varchar(100)
create table #check (CheckColumn char(6))
set
@criteria = ' like ''%your_value_here%'''
-- open cursor of all tables
declare table_cursor cursor for
select name from sysobjects where type = 'u'
order by name
open table_cursor
fetch next from table_cursor into @table
while
@@fetch_status = 0
begin
-- iterate through all columns in this table and try to find content
declare column_cursor cursor for
select '[' + c.Name + ']'
from Sysobjects o
inner join Syscolumns c on o.id = c.id
inner join systypes t on c.xtype = t.xtype
where o.type = 'U'
and o.name = @table
and t.name not like 'image'
and t.name not like 'sql_variant'
order by c.Name
print '* * * * checking ' + @table
open column_cursor
fetch next from column_cursor into @column
while @@fetch_status = 0
begin
-- see if the data is in this table/column
exec ('insert into #check select ''Exists'' from ' + @table + ' where ' + @column + @criteria)
if exists (select * from #check)
exec ('select ''ROWS EXIST'', ''' + @table + ''', ' + @column + ' from ' + @table + ' where ' + @column + @criteria)
delete from #check
fetch next from column_cursor into @column
end
close column_cursor
deallocate column_cursor
fetch next from table_cursor into @table
end
close table_cursor
deallocate table_cursor
drop
table #check
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply