locating at piece of text - 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

General SQL Server Forums

New to SQL Server Programming

locating at piece of text

Author

Topic

iajm@msn.com
Starting Member

3 Posts

Posted - 2009-03-13 : 00:30:29

This may be a bit over the head of some who are called beginners but I still consider myself a SQL newbe.

Here goes:
I found this:
declare @ColumnName varchar (50)
set @columnname = '%id%'--could be any string value
SELECT name
FROM sysobjects
WHERE id IN ( SELECT id FROM syscolumns WHERE name like(@ColumnName ))

from the result of this list of column names I need to search for a value.
for example:
if the results gave me 2 column names (lets call them "size" and "ID" )
I would like to search inside these column names for a value such as "54pp" as an exaple for ID through the use of assigning "54pp" as another variable.

Here is my attempt so no one thinks I did not work on this dilligently and try on my own:

declare
@VarColumn varchar(200),
@DbName varchar(50),
@columnName varchar (50)

set @columnName='%id%'
--set @TblName='dbo.Employees'

declare myCursor cursor
for
SELECT name
FROM sysobjects
WHERE id IN ( SELECT id
FROM syscolumns
WHERE name like( @ColumnName ))

open MyCursor

fetch next from myCursor
into @VarColumn

while (@@fetch_status=0)
begin
declare declare @TableVar table (CustomerID nchar(5) NOT NULL)
select @varColumn=#temp

select * from

fetch next from MyCursor
into @VarColumn

End
Close myCursor
DeAllocate myCursor
-------------------------------------------------------------------

declare
@columnName varchar (200),
--@maxCount int,
@counter int

set @counter =1
set @columnName= '%id%'

declare @TableVar table (counter smallint identity(1,1),CustomerID varchar (500) NOT NULL)
declare @maxcount table (counter int not null)
insert into @tableVar

SELECT name
FROM sysobjects
WHERE id IN ( SELECT id
FROM syscolumns
WHERE name like( @ColumnName ))

select * from @tableVar
insert into @maxcount select max(counter) from @tableVar
while (select [counter] from @maxcount)<=@counter
begin

select customerID from @tablevar
where counter= @counter

select * from (select CustomerID from @tableVar where @counter=counter)
where @columnName='%a%'

SET @counter= @counter + 1

end

Hello

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 00:47:31

You want to search all tables for a text match? Try this stored procedure, http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm.

Works Great ...Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

iajm@msn.com
Starting Member

3 Posts

Posted - 2009-03-13 : 10:13:48

Mohit, thanks for replying to my post, I won't be searching all of the columns for a particular piece of text although that would be tempting, I think the SQL powers that be in my company would frown. I just want to search the columns in the result set of this example query:

SELECT name
FROM sysobjects
WHERE id IN ( SELECT id
FROM syscolumns
WHERE name like( 'employeeID' ))

Hello

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 19:39:04

K... then this is what you want maybe?

declare
@tablename varchar(200),
@DbName varchar(50),
@columnName varchar (50),
@sqlstatement VARCHAR(8000),
@searchstring VARCHAR(255)

IF (OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
DROP TABLE #tmp

CREATE #tmp (TableName VARCHAR(255), ColName VARCHAR(255), ColValue VARCHAR(255)

set @columnName='%id%'
SET @searchstring = '%54pp%'
--set @TblName='dbo.Employees'

declare myCursor cursor
for
SELECT t.NAME AS TableName, c.NAME AS ColumnName
FROM sysobjects AS t, syscolumns AS C
WHERE t.id = c.id
AND name like @ColumnName

open MyCursor
fetch next from myCursor
into @tablename, @columnName

while (@@fetch_status=0)
BEGIN

SET @SQLStatement = 'INSERT INTO #tmp
SELECT ''' + @tablename + ''', ''' + @columnName + ''', ' + @columnName + '
FROM [' + @tablename + '] where @columnName like ''' + @searchstring + ''''

EXEC (@SQLStatement)

fetch next from MyCursor
into @tablename, @columnName

End
Close myCursor
DeAllocate myCursor

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

iajm@msn.com
Starting Member

3 Posts

Posted - 2009-03-13 : 22:34:59

I think that is it. I could not figure out how to make that variable into a table name. Thank you, it will take me a a day or so to digest this, this stuff does not come easy to me.

Hello

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-14 : 02:19:55

Heh you have use table variables in 2005; but since I didn't know if it was 2005 or not I choose temp table :). Feel free to post here if you have questions...

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources