June 12, 2007 at 5:30 am
SQL Query question.
what if I know what data I what to find in a database, but I don't know the table...
Do I have to do joins or something?
June 12, 2007 at 5:48 am
Is it what you are after?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=345205
_____________
Code for TallyGenerator
June 12, 2007 at 5:50 am
you have a procedure named sp_tables that will list the tables in the database. but unless and otherwise you know exactly what columns and tables to be used in your query how are you going to form the query.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 12, 2007 at 5:53 am
Sergiy,
The link you have posted is ok. But there the user knew atleast his column name. Here it looks like our friend neither knows the column name nor the table name.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 12, 2007 at 6:04 am
I don't think you read that post attentively enough.
That script searches for a value in all columns of all user tables.
_____________
Code for TallyGenerator
June 12, 2007 at 6:13 am
Thanks very much. You guys are awesme. The code from that previous post looks like what I'm looking for - but I get this error when I execute it:
Server: Msg 208, Level 16, State 1, Line 7
Invalid object name 'sys.objects'.
Server: Msg 208, Level 16, State 1, Line 7
Invalid object name 'sys.columns'.
and here's the code:
create table #tmp (table_name varchar(50), column_name varchar(50), num_records int)
declare @table_name varchar(50), @column_name varchar(50), @sql varchar(8000)
set @sql = 'IT Recovery'
DECLARE table_cursor CURSOR FOR
SELECT o.name, c.name
FROM sys.objects o
inner join sys.columns c
on o.object_id = c.object_id
where
o.type <> 's' and c.system_type_id <> 61 --non system tables and non date fields
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name, @column_name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- piece together the dynamic sql
select @sql = 'insert into #tmp select ''' + @table_name + ''', ''' + @column_name + ''', count(*) from ' + @table_name + ' where ' + @column_name + ' = ''20202'' '
--execute the sql statement
exec( @sql)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor
INTO @table_name, @column_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
--return the records where this value occurs
select * from #tmp where num_records <> 0
drop table #tmp
GO
June 12, 2007 at 6:24 am
sys.objects and sys.columns are from system catalog in SQL2005.
For SQL2000 use sysobjects and syscolumns.
_____________
Code for TallyGenerator
June 12, 2007 at 7:01 am
Thank you. Changed the query to dbo.sysobjects and dbo.syscolumns and I'm not getting this:
Invalid column name 'object_id'
June 12, 2007 at 7:13 am
Just open tables sysobjects and syscolumns and change the names in the query correspondingly.
id, xtype, etc.
_____________
Code for TallyGenerator
June 12, 2007 at 7:26 am
One more time.. : )
Here's what I changed my query to: (looking for text 'RECOVERY')
drop table #tmp
go
create table #tmp (table_name varchar(50), column_name varchar(50), num_records int)
declare @table_name varchar(50), @column_name varchar(50), @sql varchar(8000)
set @sql = 'RECOVERY'
DECLARE table_cursor CURSOR FOR
SELECT o.name, c.name
FROM dbo.sysobjects o
inner join dbo.syscolumns c
on o.id = c.id
where
o.type <> 's' and c.xtype <> 61 --non system tables and non date fields
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name, @column_name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- piece together the dynamic sql
select @sql = 'insert into #tmp select ''' + @table_name + ''', ''' + @column_name + ''', count(*) from ' + @table_name + ' where ' + @column_name + ' = ''20202'' '
--execute the sql statement
exec( @sql)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor
INTO @table_name, @column_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
--return the records where this value occurs
select * from #tmp where num_records <> 0
drop table #tmp
GO
and here are my results:
spt_provider_types best_match 15
spt_provider_types fixed_prec_scale 8
spt_provider_types is_long 3
MSreplication_options value 2
I don't really understand the results that I got.
June 12, 2007 at 7:44 am
According to the definition of table #tmp it's
table_name, column_name, num_records
where you've got value '20202'.
_____________
Code for TallyGenerator
June 12, 2007 at 8:55 am
Just an FYI .. I found what I was looking for here:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Thanks!!!!
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply