I have one specific string, such as "123abcd" for example but I don't know the name of the table or even the name of the column inside the table on my SQL Server Database. I want to find it with a select and show all columns of the related string, so I was wondering something like:
select * from Database.dbo.* where * like '%123abcd%'
For obvious reasons it doens't work, but there is a simple way to create a select statement to do something like this?
This question is related to
sql
database
sql-server-2008
select
Common Resource Grep (crgrep) will search for string matches in tables/columns by name or content and supports a number of DBs, including SQLServer, Oracle and others. Full wild-carding and other useful options.
It's opensource (I'm the author).
I usually use information_Schema.columns
and information_schema.tables
, although like @yuck said, sys.tables
and sys.columns
are shorter to type.
In a loop, concatenate these
@sql = @sql + 'select' + column_name +
' from ' + table_name +
' where ' + column_name ' like ''%''+value+''%' UNION
Then execute the resulting sql.
In oracle you can use the following sql command to generate the sql commands you need:
select
"select * "
" from "||table_name||
" where "||column_name||" like '%123abcd%' ;" as sql_command
from user_tab_columns
where data_type='VARCHAR2';
Here are couple more free tools that can be used for this. Both work as SSMS addins.
ApexSQL Search – 100% free - searches both schema and data in tables. Has couple more useful options such as dependency tracking…
SSMS Tools pack – free for all versions except SQL 2012 – doesn’t look as advanced as previous one but has a lot of other cool features.
create procedure usp_find_string(@string as varchar(1000))
as
begin
declare @mincounter as int
declare @maxcounter as int
declare @stmtquery as varchar(1000)
set @stmtquery=''
create table #tmp(tablename varchar(128),columnname varchar(128),rowid int identity)
create table #tablelist(tablename varchar(128),columnname varchar(128))
declare @tmp table(name varchar(128))
declare @tablename as varchar(128)
declare @columnname as varchar(128)
insert into #tmp(tablename,columnname)
select a.name,b.name as columnname from sysobjects a
inner join syscolumns b on a.name=object_name(b.id)
where a.type='u'
and b.xtype in(select xtype from systypes
where name='text' or name='ntext' or name='varchar' or name='nvarchar' or name='char' or name='nchar')
order by a.name
select @maxcounter=max(rowid),@mincounter=min(rowid) from #tmp
while(@mincounter <= @maxcounter )
begin
select @tablename=tablename, @columnname=columnname from #tmp where rowid=@mincounter
set @stmtquery ='select top 1 ' + '[' +@columnname+']' + ' from ' + '['+@tablename+']' + ' where ' + '['+@columnname+']' + ' like ' + '''%' + @string + '%'''
insert into @tmp(name) exec(@stmtquery)
if @@rowcount >0
insert into #tablelist values(@tablename,@columnname)
set @mincounter=@mincounter +1
end
select * from #tablelist
end
I think you have to options:
Build a dynamic SQL using sys.tables
and sys.columns
to perform the search (example here).
Use any program that have this function. An example of this is SQL Workbench (free).
Here is an easy and convenient cursor based solution
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'StringtoSearch'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']
LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
SQL Locator (free) has worked great for me. It comes with a lot of options and it's fairly easy to use.
Source: Stackoverflow.com