how to all tables - 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

how to all tables

Author

Topic

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-14 : 23:17:10

hi,

1)i want to list out the tables which is in my database.
2)i have column(emp_id) in some tables in my database.so which tables having emp_id as a column i want to list out those tables which is in my satabase,please give me the query for this,can any one help me

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 23:25:51

You can query the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS views to find what you want.

CODO ERGO SUM

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-14 : 23:58:35

hi in 10 tables i have column emp_id and in that 10 table's column)emp_id) i have one emp_id as 10.
i used this and i found the table names:

select table_name from information_schema.columns where column_name='name'

but now what i need is i have record karthik in every name column in 10 tables.so i want to retreive the tables which has the name as column and has karthik as record.so please give me query for this

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-15 : 00:50:38

please can any one reply for my question

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-12-15 : 00:56:16

If I can clarify the question:
You have several tables that contain a column called 'emp_id'. You want to return all such records with a given emp_id.

If that's the case, the information that Michael gave you should be good enough to do what you want. You'll either need to write a simple looping statement to query each of the tables, or you can get a list of such tables and write your own queries eg:

SELECT * FROM table1 WHERE emp_id = 'your search field'
SELECT * FROM table2 WHERE emp_id = 'your search field'
SELECT * FROM table3 WHERE emp_id = 'your search field'

Also a little courtesy goes a long way when asking for help....

HTH,

Tim

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-15 : 02:36:47

hi timmy,
i want to list the tables which has the column name as emp_id which has the record as 10.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 03:42:24

Run this , copy the result back to QA, and run them

Select 'Select * from '+table_name+' where emp_id=10' from information_schema.columns where column_name='emp_id'

But, why do you need this type of query?

Madhivanan

Failing to plan is Planning to fail

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-15 : 04:27:58

hi madhivannan,
if i run your query it gives ans like this,
the query is :

Select 'Select * from '+table_name+' where name=karthik' from information_schema.columns where column_name='name'

Select * from chk_view where name=karthik
Select * from v1 where name=karthik
Select * from mun where name=karthik
Select * from emp3 where name=karthik
Select * from emp where name=karthik
Select * from emp1 where name=karthik
Select * from table where name=karthik
Select * from vijay2 where name=karthik
Select * from tab where name=karthik
Select * from examp where name=karthik
Select * from aa where name=karthik
Select * from karhtik where name=karthik
Select * from empdetail where name=karthik
Select * from prac where name=karthik
Select * from iii where name=karthik
Select * from AMM where name=karthik
Select * from emp2 where name=karthik
Select * from syssegments where name=karthik
Select * from loop where name=karthik

i need the table name,why it didn't give the table name which has the column name which has the karthik as a record.can u give query
or can please give cursor code for this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 06:07:54

This may help you
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-15 : 06:19:28

This can be Quick-n-Dirty solution for your problem:

select identity(int, 1,1) seq, o.[name] Tbl, 0 as Valid into #temp from syscolumns c join sysobjects o on c.[id] = o.[id] and c.[name] = 'emp_id' and o.xtype = 'U'

Declare @i int, @max int,
	@str varchar(8000)

select @i = min(seq), @max = max(seq) from #temp

while @i <= @max
begin
	set @str = 'update #temp set Valid = 1 where exists (select * from ' + (select tbl from #temp where seq = @i) + ' where emp_id = 10 ) and seq = ' + convert(varchar(5), @i)
	exec(@str)
	set @i = @i + 1
end

select Tbl from #temp where Valid = 1

drop table #temp

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-12-15 : 23:23:45

hey guys,thankyou very much for your replies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources