Select from 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

Select from all tables

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-13 : 15:09:35

The client has a db with about 90 tables. I'm looking to identify specific data from a common column between most tables. Most, but not all tables contain a column called 'personid'. I would like to be able to query all tables in the db which have that column for a specific personID. If any table does not have that column, the query should report that and jump to the next table and report the data from that column. Is there a system sproc or something similar which will work for this?

thanks

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-02-13 : 15:40:13

You could use:
use [your db]
SELECT 'select * from ' + rtrim(so.name) + ' where personID = 12345'
FROM dbo.sysobjects so
join dbo.syscolumns sc on sc.id = so.id
where so.xtype = 'u' and sc.name = 'personID'

when you could execute the resulting output or put the output into a cursor and execute each record from the result set.

Likes to run, hates the runs!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 15:44:28

Do you want to look for a specific person ID.

What Data do you want to see returned?

Just the tables with that ID, or the entire row or...?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-13 : 16:09:59

i'm looking to see the entire row from each table that has an entry with that PersonID.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-02-14 : 02:44:08

You may need to use something like this
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 09:56:35

is personId int?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 10:10:28

How about this


DECLARE @COLUMN_NAME varchar(256), @COL_VALUE  varchar(25), @sql varchar(8000)

SELECT @COLUMN_NAME = 'AKA_ID', @COL_VALUE = 'X002548'


DECLARE myCursor99 CURSOR FOR

	 SELECT 'SELECT * FROM [' + t.TABLE_CATALOG + '].[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']' 
	 + CHAR(13) + CHAR(10)
--	 + '   WHERE [' + @COLUMN_NAME + '] = ' +  @COL_VALUE					-- For Numeric Values 
 	 + ' WHERE [' + @COLUMN_NAME + '] = ' +  '''' + @COL_VALUE + ''''		-- For Char Values
AS [SQL]
	   FROM INFORMATION_SCHEMA.Tables t
 INNER JOIN INFORMATION_SCHEMA.Columns c
		 ON t.TABLE_CATALOG = c.TABLE_CATALOG
		AND t.TABLE_SCHEMA	= c.TABLE_SCHEMA
		AND t.TABLE_NAME	= c.TABLE_NAME
	  WHERE t.TABLE_TYPE	= 'BASE TABLE'
	    AND c.COLUMN_NAME   = @COLUMN_NAME
	    
OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @SQL

WHILE @@FETCH_STATUS = 0
  BEGIN
	PRINT(@SQL)
	EXEC(@SQL)
	FETCH NEXT FROM myCursor99 INTO @SQL
  END
  
CLOSE myCursor99
DEALLOCATE myCursor99

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-14 : 10:14:24

wow. didn't expect it to be that complex. Thanks. Will give it a go and report back.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 10:16:17

just change the column name and the value in the SELECT for whatever you want

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources