Information Schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Information Schema

Author

Topic

psteja2000
Starting Member

9 Posts

Posted - 2008-01-04 : 09:47:34

I am kinda new to tsql programming and trying to find a quickie from google and such for a solution.

What I want is to get a list of tables and columns that contain a certain string. I actually want to replace all such occuranaces with some other string but here is how I started:


Declare @tn varchar(100)
Declare @cn varchar(100) 
Declare mycursor cursor for
select top 3 table_name , column_name from information_schema.columns where data_type like '%varchar';
Open mycursor; 
fetch next from mycursor into @tn,@cn;
while @@fetch_status = 0
begin 
if exists (select @cn from @tn where @cn like '%MYSEARCHSTRING%')	print @cn + ':' + @tn ; 
fetch next from mycursor into @tn,@cn; 
end
close mycursor
deallocate mycursor

When I try to run ths, it complains that table variable @tn is not declared. Obviously I cann't use a varchar variable in place of a table, looks like..

Can anyone point out how I should change the above? I basically want to use the @tn and @cn as table name and column name in my 'select' inside the loop. HOW???

Thanks for your time.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-01-04 : 10:10:35

I'm not sure exactly what you're looking for. This returns a list of tables that have a column named somthing like 'Whatever'
DECLARE @sString varchar(50)

SET @sString = 'Whatever'

select Column_Name + ':' +Table_Name from
information_schema.columns
WHERE
Data_type = 'varchar'
and column_name like '%'+@sString +'%'

Also, look at table variables in BOL, and get ready for people to warn you against cursors!

Good Luck,

Jim

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-04 : 10:15:41

searching all columns in all tables for a string is rather inefficient. it's better if you know what table and column holds the data you want.

however there are ways to do what you want. i'm sure the performance is terrible on large databases (i've never had a need for this so never tried it):
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

elsasoft.org

psteja2000
Starting Member

9 Posts

Posted - 2008-01-04 : 10:50:22

Jimf,

I didn't explain properly.. What I want to do is (eventually) is,

given a search string, I want to go to all tables in a given database see if any column in it (not the column name - I guess I should get my column/field/cell terminology usage more precise) has this as its value and if so , replace it with some other replacement string.

This is a one time thing I am trying to do, so performance/efficienty is not a big issue at all.

This comes in handy when you say are setting up a test database adn quickly want to change the test data. Atleast that's my case.

Thank you both for your time.

I will look at the link in the second post and will get back.

psteja2000
Starting Member

9 Posts

Posted - 2008-01-04 : 10:58:51

jezemine,

That link is exactly what I am looking for, I can now make necessary modifications to it. Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources