Find a key in 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

Find a key in all tables

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-10-28 : 08:15:30

I have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a certain POLICY_NUMBER

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 08:17:24

See if this will help: 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

divan
Posting Yak Master

153 Posts

Posted - 2013-10-28 : 09:30:26

I phrased my question wrong..
What I should have said was have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a POLICY_NUMBER = 12345..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 09:44:30

You can use INFORMATION_SCHEMA.COLUMNS metadata view for that. use query like below


SELECT DISTINCT 'SELECT ''' + TABLE_NAME + ''' AS TABLENAME,COUNT(*) AS Cnt FROM ' + TABLE_NAME + ' WHERE POLICY_NUMBER = 12345 '
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'POLICY_NUMBER'

You'll get as output the required script which you will copy and paste in a new window and execute to get Table names along with count of records with required value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

divan
Posting Yak Master

153 Posts

Posted - 2013-10-28 : 10:26:30

Thanks this worked great...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 12:36:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources