Search data in all table - 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

Search data in all table

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-03-08 : 08:35:05

How can I search for records in table?
Have too many tables to search one by one.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-08 : 08:39:05

Take a look at this article: http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx

Also, Red Gate has a search product - SQL Search (which I think is free)

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-03-09 : 01:23:52

Thanks.

It takes too long to execute.
Is there any way to distinct only 1 table?

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-09 : 02:34:59

select 'insert #a select count(*),'''+name+''' from '+name+ ' where condition' from sys.tables
create table #a(cnt int,tabname varchar(100))

1)execute selct statement
2)create table
3)copy and paste the resultset and execute

hope this helps

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-03-09 : 03:10:44

What does the where condition meant?
How can I apply to search for data?

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-09 : 03:41:50

Post sample query for a table

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-03-09 : 04:01:20

I would want to search for tables which has data.

For example : table which has this the data 'Grace'

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-09 : 04:23:16

create table #a(cnt int,tabname varchar(100))
select 'insert #a select count(*),'''+name+''' from '+name+ ' where columnname = grace' from sys.tables

select * from #a where cnt >0
you will get table list which has data = grace

hope this helps

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-03-09 : 04:40:33

Is not working.
This query publish the whole table name with the the where condition nomatter it has Grace or either way.

egrouchko
Starting Member

1 Post

Posted - 2012-07-17 : 12:24:04

unspammed

X002548
Not Just a Number

15586 Posts

Posted - 2012-07-17 : 14:53:56

Generate this and modify tit for what you need


SELECT * 
  FROM INFORMATION_SCHEMA.Columns
 WHERE DATA_TYPE IN (
  'char'
, 'nvarchar'
, 'varchar'
-- , 'text'
)
AND CHARACTER_MAXIMUM_LENGTH > 15
AND COLUMN_NAME NOT LIKE 'Filler%'

SELECT 'SELECT ' + '''' + '[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '].[' + COLUMN_NAME + ']' + '''' +' AS SOURCE'
	+  ', [' + COLUMN_NAME + '] '													+CHAR(13)+CHAR(10)
	+  '  FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME+ '] ' +CHAR(13)+CHAR(10)
	+  ' WHERE [' + COLUMN_NAME + '] LIKE ' + '''' + 'GLO%' + ''''	+CHAR(13)+CHAR(10)
	+  'UNION '																	+CHAR(13)+CHAR(10)
  FROM INFORMATION_SCHEMA.Columns c 
 WHERE DATA_TYPE IN (
  'char'
, 'nvarchar'
, 'varchar'
--, 'text'
)
AND CHARACTER_MAXIMUM_LENGTH > 15
AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables t 
			 WHERE TABLE_TYPE = 'BASE TABLE'
			   AND t.TABLE_CATALOG = c.TABLE_CATALOG
			   AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
			   AND t.TABLE_NAME = c.TABLE_NAME)
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources